package net.servir.censo.dao.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import net.servir.censo.util.SMTPSend;
import net.servir.censo.dao.ServicioCivilDAO;

//--- jp ---
import net.servir.censo.model.*;

import oracle.jdbc.OracleTypes;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class JdbcServicioCivilDAO implements ServicioCivilDAO {

	private SimpleJdbcTemplate jdbcTemplate;
	private DataSource dataSourceCenso;
	private CallableStatement callableStatement = null;
	private Connection conn = null;
	
	@Autowired
	public JdbcServicioCivilDAO(DataSource dataSource){
		this.jdbcTemplate = new SimpleJdbcTemplate(dataSource);
		this.dataSourceCenso = dataSource;
	} 
	
	//--- jp ---
	public LinkedList<Estudios> ObtenerEstudios() { 
		List<Map<String, Object>> lSectorMaps = 
			jdbcTemplate.queryForList("select codigo, trim(descripcion) as desc_estudios " +
					" from codigo where grupo = 'ESTU' and estado = 1 order by orden");
		LinkedList<Estudios> obj = new LinkedList<Estudios>();
		for(Map<String, Object> rec : lSectorMaps){
			Estudios s = new Estudios();
			s.setCod_estudios((String)rec.get("codigo"));
			s.setDesc_estudios((String)rec.get("desc_estudios"));			
			obj.add(s);
		} 
		return obj;
	}
	
	public LinkedList<Snip> ObtenerSnip() { 
		List<Map<String, Object>> lSectorMaps = 
			jdbcTemplate.queryForList("select codigo, trim(descripcion) as desc_snip " +
					" from codigo where grupo = 'ADES' and estado = 1 order by orden");
		LinkedList<Snip> obj = new LinkedList<Snip>();
		for(Map<String, Object> rec : lSectorMaps){
			Snip s = new Snip();
			s.setCod_experiencia_snip((String)rec.get("codigo"));
			s.setDesc_snip((String)rec.get("desc_snip"));			
			obj.add(s);
		} 
		return obj;
	}
	
	public LinkedList<MayorGradoAcademico> ObtenerMayorGradoAcademico() { 
		List<Map<String, Object>> lSectorMaps = 
			jdbcTemplate.queryForList("select codigo, trim(descripcion) as desc_grado_academico " +
					" from codigo where grupo = 'GRAD' and estado = 1 order by orden");
		LinkedList<MayorGradoAcademico> obj = new LinkedList<MayorGradoAcademico>();
		for(Map<String, Object> rec : lSectorMaps){
			MayorGradoAcademico s = new MayorGradoAcademico();
			s.setCod_grado_academico((String)rec.get("codigo"));
			s.setDesc_grado_academico((String)rec.get("desc_grado_academico"));			
			obj.add(s);
		} 
		return obj;
	}
	

	
	
	public String ObtenerTituloUniversitario(Number idServicioCivil) { 
		String titulo_universitario="";
		List<Map<String, Object>> lSectorMaps = 
			jdbcTemplate.queryForList("select titulo_universitario from servicio_civil where id_servicio_civil='"+idServicioCivil.toString()+"'");
		for(Map<String, Object> rec : lSectorMaps){
			titulo_universitario=(String)rec.get("titulo_universitario");		
		} 
		if(titulo_universitario==null){
			titulo_universitario="";
		}
		return titulo_universitario;
	}
	
	//--- fin ---
	
	public LinkedList<Regimen> ObtenerRegimen() { 
		List<Map<String, Object>> lSectorMaps = 
			jdbcTemplate.queryForList("select codigo, trim(descripcion) as desc_regimen " +
					" from codigo where grupo = 'REGI' and estado = 1 order by orden");
		LinkedList<Regimen> obj = new LinkedList<Regimen>();
		for(Map<String, Object> rec : lSectorMaps){
			Regimen s = new Regimen();
			s.setCod_regimen((String)rec.get("codigo"));
			s.setDesc_regimen((String)rec.get("desc_regimen"));			
			obj.add(s);
		} 
		return obj;
	}
	
	public LinkedList<TipoObservacion> ObtenerTipoObservacion()
	{
		List<Map<String, Object>> lSectorMaps = 
			jdbcTemplate.queryForList("select codigo, trim(descripcion) as descripcion " +
					" from codigo where grupo = 'TOBS' and estado = 1 order by orden");
		LinkedList<TipoObservacion> obj = new LinkedList<TipoObservacion>();
		for(Map<String, Object> rec : lSectorMaps){
			TipoObservacion s = new TipoObservacion();
			s.setCod_observacion((String)rec.get("codigo"));
			s.setDescripcion((String)rec.get("descripcion"));			
			obj.add(s);
		} 
		return obj;
	}
		

	public LinkedList<Profesion> ObtenerProfesion() { 
		List<Map<String, Object>> lSectorMaps = 
			jdbcTemplate.queryForList("select codigo, trim(descripcion) as desc_profesion " +
					" from codigo where grupo = 'PROF' and estado = 1 order by orden");
		LinkedList<Profesion> obj = new LinkedList<Profesion>();
		for(Map<String, Object> rec : lSectorMaps){
			Profesion s = new Profesion();
			s.setCod_profesion((String)rec.get("codigo"));
			s.setDesc_profesion((String)rec.get("desc_profesion"));			
			obj.add(s);
		}
		return obj;
	}
	
	public LinkedList<TipoDocumento> ObtenerTipoDocumento() { 
		List<Map<String, Object>> lSectorMaps = 
			jdbcTemplate.queryForList("select codigo, trim(descripcion) as desc_tipo_doc " +
					" from codigo where grupo = 'TDOC' and estado = 1 order by orden");
		LinkedList<TipoDocumento> obj = new LinkedList<TipoDocumento>();
		for(Map<String, Object> rec : lSectorMaps){
			TipoDocumento s = new TipoDocumento();
			s.setCod_tipo_doc((String)rec.get("codigo"));
			s.setDesc_tipo_doc((String)rec.get("desc_tipo_doc"));			
			obj.add(s);
		}
		return obj;
	}
	
	
	public List ObtenerFunciones(Number idServicioCivil) {

		List funciones = null;
		idServicioCivil = idServicioCivil==null?0:idServicioCivil;
		String sql="select eta.id_etapa, eta.descripcion as desc_etapa" +
				", net_core.f_funciones_participante("+idServicioCivil.toString()+",  fun.id_funcion) as flag_funcion" +
				", fun.id_funcion, fun.descripcion as desc_funcion " +
				"from etapa eta " +
				"left join funcion fun on eta.id_etapa = fun.id_etapa order by fun.id_funcion";		
		
		List<Map<String, Object>> lEntidadMaps = jdbcTemplate.queryForList(sql);
		
		for(Map<String, Object> obj : lEntidadMaps){
			EtapaFuncion g = new EtapaFuncion();
			g.setId_etapa((Number)obj.get("id_etapa"));
			g.setDesc_etapa((String)obj.get("desc_etapa"));
			g.setId_funcion((Number)obj.get("id_funcion"));
			g.setDesc_funcion((String)obj.get("desc_funcion"));
			g.setFlag_funcion((String)obj.get("flag_funcion"));			
			if (funciones == null) funciones = new ArrayList();
				funciones.add(g);			
			g = null;			
		}
		return funciones;

	}
	
	ServicioCivilExtractor servicioCivilExtractor = new ServicioCivilExtractor();
	public ServicioCivil ObtenerServicioCivil(Number idServicioCivil)
	{ 
		// mmm3
		String sql="SELECT sci.ID_SERVICIO_CIVIL, sci.CORREO, sci.CORREO_ALTERNO,sci.COD_EXPERIENCIA_SNIP ,sci.COD_ESTUDIOS ,sci.COD_GRADO_ACADEMICO ,sci.ID_PUESTO_TIPO, sci.TELEFONO, to_char(sci.FEC_OBSERVACION,'DD/MM/YYYY') as FEC_OBSERVACION, sci.PLAZO" +
				", to_char(sci.FEC_SUBSANACION,'DD/MM/YYYY') as FEC_SUBSANACION, sci.DETALLE_OBSERVACION, nvl(sci.ESTADO_OBSERVACION, '0') as ESTADO_OBSERVACION, sci.ID_EJECUTORA" +
				", sci.COD_PROFESION, sci.COD_REGIMEN, sci.CLAVE, sci.DETALLE_SUBSANACION, to_char(sci.FEC_REGISTRO,'DD/MM/YYYY') as FEC_REGISTRO" +
				", sci.COD_ESTADO, sci.DNI , sci.DNIPREVIO, sci.id_persona, sci.jefe, sca.id_ambito " +
				", per.NOMBRES, per.APELLIDO_PATERNO, per.APELLIDO_MATERNO, to_char(per.FEC_NACIMIENTO,'DD/MM/YYYY') as FEC_NACIMIENTO, per.SEXO, per.COD_TIPO_DOC " +
				", per.ESTADO_VALIDACION, sci.cod_tipo_observacion, sci.estado_subsanacion, sci.id_responsable, sci.estado_equipo " +
				", sci.otrosestudios, sci.universidad,SCI.ID_SISTEMA_ETAPA, SCI.COD_EXPERIENCIA_MATERIA, SCI.TITULO_UNIVERSITARIO "+
				", sci.id_universidad, sci.coddep, sci.codpro, sci.coddis, sci.certificado, sci.codcertificado, sci.direccion "+
				", sci.cod_experiencia_general, sci.id_tipo_estudio " +
				" FROM SERVICIO_CIVIL sci, PERSONA per,  SERVICIO_CIVIL_AMBITO sca" + 
				" WHERE sci.dni = per.dni AND sci.ID_PERSONA = per.ID_PERSONA and sci.ID_SERVICIO_CIVIL = sca.ID_SERVICIO_CIVIL ";
		if (idServicioCivil!= null && idServicioCivil.intValue()>0)
			sql = sql + " AND sci.ID_SERVICIO_CIVIL = " + idServicioCivil.toString();
		Object obj  = jdbcTemplate.getJdbcOperations().query(sql,  new Object[]{}, servicioCivilExtractor);
		if (obj != null)
			return (ServicioCivil)obj;
		
		return null;
		}
	
	
	public String ObtenerIdNivelGobierno(Number id_ejecutora) { 
		String titulo_universitario="";
		List<Map<String, Object>> lSectorMaps = 
			jdbcTemplate.queryForList("SELECT ID_NIVEL_GOBIERNO FROM RESPONSABLE_EJECUTORA_AMBITO WHERE ID_EJECUTORA='"+id_ejecutora+"'");
		for(Map<String, Object> rec : lSectorMaps){
			titulo_universitario=(String)rec.get("titulo_universitario");		
		} 
		if(titulo_universitario==null){
			titulo_universitario="";
		}
		return titulo_universitario;
	}
		
		private class ServicioCivilExtractor implements ResultSetExtractor {
		public Object extractData(ResultSet rs) throws SQLException,
				DataAccessException {			
			return mapServicioCivil(rs);
		}
	}
		
		public ServicioCivil ObtenerServicioCivilPorDNI(String dni, Number id_persona)
		{ 
			String sql="SELECT sci.ID_SERVICIO_CIVIL, sci.CORREO, sci.TELEFONO, to_char(sci.FEC_OBSERVACION,'DD/MM/YYYY') as FEC_OBSERVACION, sci.PLAZO" +
					", to_char(sci.FEC_SUBSANACION,'DD/MM/YYYY') as FEC_SUBSANACION, sci.DETALLE_OBSERVACION, nvl(sci.ESTADO_OBSERVACION, '0') as ESTADO_OBSERVACION, sci.ID_EJECUTORA" +
					", sci.COD_PROFESION, sci.COD_REGIMEN, sci.CLAVE, sci.DETALLE_SUBSANACION, to_char(sci.FEC_REGISTRO,'DD/MM/YYYY') as FEC_REGISTRO" +
					", sci.COD_ESTADO, sci.DNI , sci.DNIPREVIO, sci.id_persona, sci.jefe, sca.id_ambito " +
					", per.NOMBRES, per.APELLIDO_PATERNO, per.APELLIDO_MATERNO, to_char(per.FEC_NACIMIENTO,'DD/MM/YYYY') as FEC_NACIMIENTO, per.SEXO, per.COD_TIPO_DOC " +
					", per.ESTADO_VALIDACION, sci.cod_tipo_observacion, sci.estado_subsanacion " +
					", sci.otrosestudios, sci.universidad "+
					" FROM SERVICIO_CIVIL sci, PERSONA per,  SERVICIO_CIVIL_AMBITO sca" +
					" WHERE sci.dni = per.dni AND sci.ID_PERSONA = per.ID_PERSONA and sci.ID_SERVICIO_CIVIL = sca.ID_SERVICIO_CIVIL ";
			if (dni!= null && id_persona.intValue()>0)
				sql = sql + " AND sci.dni = '" + dni.toString() + "' and sci.id_persona = " +  id_persona.toString();
			Object obj  = jdbcTemplate.getJdbcOperations().query(sql,  new Object[]{}, servicioCivilExtractor);
			if (obj != null)
				return (ServicioCivil)obj;
			
			return null;
			}
			
			private class ServicioCivilExtractor1 implements ResultSetExtractor {
			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {			
				return mapServicioCivil(rs);
			}
		}	
		
	private ServicioCivil mapServicioCivil(ResultSet rs) throws SQLException{
			ServicioCivil servicioCivil = null;			
		while(rs.next()){
			if(servicioCivil==null){
				servicioCivil = new ServicioCivil();				
				servicioCivil.setId_servicio_civil(rs.getInt("ID_SERVICIO_CIVIL"));
				servicioCivil.setId_persona(rs.getInt("ID_PERSONA"));
				servicioCivil.setCorreo(rs.getString("CORREO"));
				servicioCivil.setTelefono(rs.getString("TELEFONO"));
				servicioCivil.setFec_observacion(rs.getString("FEC_OBSERVACION"));
				servicioCivil.setPlazo(rs.getInt("PLAZO"));
				servicioCivil.setFec_subsanacion(rs.getString("FEC_SUBSANACION"));
				servicioCivil.setDetalle_observacion(rs.getString("DETALLE_OBSERVACION"));
				servicioCivil.setEstado_observacion(rs.getString("ESTADO_OBSERVACION"));
				servicioCivil.setId_ejecutora(rs.getInt("ID_EJECUTORA"));
				servicioCivil.setCod_profesion(rs.getString("COD_PROFESION"));
				servicioCivil.setCod_regimen(rs.getString("COD_REGIMEN"));
				servicioCivil.setClave(rs.getString("CLAVE"));
				servicioCivil.setDetalle_subsanacion(rs.getString("DETALLE_SUBSANACION"));
				servicioCivil.setFec_registro(rs.getString("FEC_REGISTRO"));
				servicioCivil.setCod_estado(rs.getString("COD_ESTADO"));
				servicioCivil.setDni(rs.getString("DNI"));
				servicioCivil.setDniPrevio(rs.getString("DNIPREVIO"));
				servicioCivil.setNombres(rs.getString("NOMBRES"));
				servicioCivil.setApellido_paterno(rs.getString("APELLIDO_PATERNO"));
				servicioCivil.setApellido_materno(rs.getString("APELLIDO_MATERNO"));
				servicioCivil.setFec_nacimiento(rs.getString("FEC_NACIMIENTO"));
				servicioCivil.setSexo(rs.getString("SEXO"));
				servicioCivil.setCod_tipo_doc(rs.getString("COD_TIPO_DOC"));
				servicioCivil.setJefe(rs.getString("JEFE"));
				servicioCivil.setId_ambito((Number)(rs.getInt("ID_AMBITO")));
				servicioCivil.setId_puesto_tipo((Number)(rs.getInt("ID_PUESTO_TIPO")));
				servicioCivil.setEstado_validacion(rs.getString("ESTADO_VALIDACION"));
				servicioCivil.setCod_tipo_observacion(rs.getString("COD_TIPO_OBSERVACION"));
				servicioCivil.setEstado_subsanacion(rs.getString("ESTADO_SUBSANACION"));
				servicioCivil.setEstado_equipo(rs.getString("ESTADO_EQUIPO"));
				
				servicioCivil.setCorreo_alterno(rs.getString("CORREO_ALTERNO"));
				servicioCivil.setCod_estudios(rs.getString("COD_ESTUDIOS"));
				servicioCivil.setCod_experiencia_snip(rs.getString("COD_EXPERIENCIA_SNIP"));
				servicioCivil.setCod_grado_academico(rs.getString("COD_GRADO_ACADEMICO"));
				
				servicioCivil.setOtrosestudios(rs.getString("OTROSESTUDIOS"));
				servicioCivil.setUniversidad(rs.getString("UNIVERSIDAD"));
				servicioCivil.setTitulo_universitario(rs.getString("TITULO_UNIVERSITARIO"));
				
				servicioCivil.setId_sistema_etapa((Number)(rs.getInt("ID_SISTEMA_ETAPA")));
				servicioCivil.setCod_experiencia_materia(rs.getString("COD_EXPERIENCIA_MATERIA"));
				
				servicioCivil.setId_universidad((Number)(rs.getInt("ID_UNIVERSIDAD")));
				servicioCivil.setCoddepdir(rs.getString("CODDEP"));
				servicioCivil.setCodprodir(rs.getString("CODPRO"));
				servicioCivil.setCoddisdir(rs.getString("CODDIS"));
				servicioCivil.setCertificado(rs.getString("CERTIFICADO"));
				servicioCivil.setCodcertificado(rs.getString("CODCERTIFICADO"));
				servicioCivil.setDireccion(rs.getString("DIRECCION"));
				
				servicioCivil.setCod_experiencia_general(rs.getString("COD_EXPERIENCIA_GENERAL"));
				
				servicioCivil.setId_tipo_estudio((Number)(rs.getInt("ID_TIPO_ESTUDIO")));
			}
			//otras tablas
		}
		if(servicioCivil==null){
			//throw new EmptyResultDataAccessException(1);
		}
		return servicioCivil;
		}
		
	

		public String AgregarServicioCivil(ServicioCivil obj)
		{
			String mensaje="";
			//try {	
				// Add "Persona"
				Integer seqIdPersona = AddPersona(obj);
				// Get last Id  "Servicio_Civil"
				obj.setId_persona(seqIdPersona);
				Number idServicioCivil=GetLastIdServicioCivil();
				// Add "Servicio_Civil"
				AddServicioCivil(obj, idServicioCivil);
				mensaje = idServicioCivil.toString();
/*			}
			catch (Exception e) {
				// TODO: handle exception
				System.out.println(e.getMessage());
			}	
*/			return mensaje;
		}
		
		public Integer AddPersona(ServicioCivil obj)		
		{
			//Verificamos si existe el registro de la persona
			//String sql = "select count(*)  from persona where dni = ? and id_persona =" + obj.getId_persona();
			String sql = "select count(*) from persona where dni = ? " ;
			Integer seq = obj.getId_persona().intValue();
			int iCont = jdbcTemplate.queryForInt(sql,obj.getDni());
			if (iCont == 0) {
//				System.out.println("Sexo :"+obj.getSexo());
				sql = "SELECT SEQ_ID_PERSONA.nextval FROM DUAL";
				seq = jdbcTemplate.queryForInt(sql);
				sql = "INSERT INTO PERSONA(DNI, NOMBRES, APELLIDO_PATERNO" +
				", APELLIDO_MATERNO, FEC_NACIMIENTO, SEXO, COD_TIPO_DOC, ID_PERSONA) " +
				" VALUES(?,?,?,?,to_date(?,'dd/mm/yyyy'),?,?," + seq.toString() + ") ";
				jdbcTemplate.update(sql, obj.getDni(), obj.getNombres(), obj.getApellido_paterno()
							, obj.getApellido_materno(), obj.getFec_nacimiento(), obj.getSexo()
							, obj.getCod_tipo_doc());
			} else {
				// Actualizamos el registro
				sql = "UPDATE PERSONA SET NOMBRES= ?, APELLIDO_PATERNO= ?" +
				", APELLIDO_MATERNO= ?, FEC_NACIMIENTO= ?, SEXO= ?, COD_TIPO_DOC= ? " +
				" WHERE trim(DNI) = trim(?) ";
				jdbcTemplate.update(sql, obj.getNombres(), obj.getApellido_paterno()
							, obj.getApellido_materno(), obj.getFec_nacimiento(), obj.getSexo() 
							,obj.getCod_tipo_doc(), obj.getDni());
				sql = "select id_persona from persona where dni = ? " ;
				seq = jdbcTemplate.queryForInt(sql,obj.getDni());
			}
			return seq; 
		}
		
		public Persona GetPersona(Responsable responsable) {
			Persona obj = null;
			
			String sql = "SELECT DNI,NOMBRES,APELLIDO_PATERNO,APELLIDO_MATERNO,to_char(FEC_NACIMIENTO,'DD/MM/YYYY') as FEC_NACIMIENTO"
					+" ,SEXO,COD_TIPO_DOC,ESTADO_VALIDACION,to_char(FEC_VALIDACION,'DD/MM/YYYY') as FEC_VALIDACION," +
					"ROL_VALIDACION, ID_PERSONA FROM PERSONA WHERE dni = '" + responsable.getDni() + "' "; // and id_persona = " + responsable.getId_persona();
			List<Map<String, Object>> lEntidadMaps = jdbcTemplate.queryForList(sql);

			for (Map<String, Object> rec : lEntidadMaps) {
				obj = new Persona();
				obj.setDni((String) rec.get("DNI"));
				obj.setId_persona((Number)rec.get("ID_PERSONA"));
				obj.setNombres((String) rec.get("NOMBRES"));
				obj.setApellido_paterno((String) rec.get("APELLIDO_PATERNO"));
				obj.setApellido_materno((String) rec.get("APELLIDO_MATERNO"));
				obj.setFec_nacimiento((String) rec.get("FEC_NACIMIENTO"));
				obj.setSexo((String) rec.get("SEXO"));
				obj.setCod_tipo_doc((String) rec.get("COD_TIPO_DOC"));
				obj.setEstado_validacion((String) rec.get("ESTADO_VALIDACION"));
				obj.setFec_validacion((String) rec.get("FEC_VALIDACION"));
				obj.setRol_validacion((String) rec.get("ROL_VALIDACION"));				
			}
			return obj;
		}			
		
		public Integer RegistrarPersona(Persona obj)
		{
			//Verificamos si existe el registro de la persona
			Integer seq = 0;
			String sql = "select count(*)  from persona where dni = ? and id_persona = ?";			
			int iCont = jdbcTemplate.queryForInt(sql,obj.getDni(), obj.getId_persona());
			
			if (iCont == 0) {
				
				sql = "SELECT SEQ_ID_PERSONA.nextval FROM DUAL";
				seq = jdbcTemplate.queryForInt(sql);
				sql = "INSERT INTO PERSONA(DNI, NOMBRES, APELLIDO_PATERNO" +
				", APELLIDO_MATERNO, FEC_NACIMIENTO, SEXO, COD_TIPO_DOC,ESTADO_VALIDACION,FEC_VALIDACION,ROL_VALIDACION, id_persona) " +
				" VALUES(?,upper(?),upper(?),upper(?),TO_DATE(?,'DD/MM/YYYY'),?,?,?,?,?," + seq.toString()  + ") ";
				jdbcTemplate.update(sql, obj.getDni(), obj.getNombres(), obj.getApellido_paterno()
							, obj.getApellido_materno(), obj.getFec_nacimiento(), obj.getSexo(), obj.getCod_tipo_doc(),obj.getEstado_validacion(),obj.getFec_validacion(),obj.getRol_validacion());
				
			} else {
				seq = obj.getId_persona().intValue();
				// Actualizamos el registro
				sql = "UPDATE PERSONA SET NOMBRES= upper(?), APELLIDO_PATERNO= upper(?)" +
				", APELLIDO_MATERNO= upper(?), FEC_NACIMIENTO= to_date(?,'DD/MM/YYYY'), SEXO = ?, COD_TIPO_DOC = ? ," +
				" ESTADO_VALIDACION = ?,FEC_VALIDACION = to_date(?,'DD/MM/YYYY'),ROL_VALIDACION = ? " +
				" WHERE trim(DNI) = trim(?) and id_persona = ?";
				jdbcTemplate.update(sql, obj.getNombres(), obj.getApellido_paterno()
							, obj.getApellido_materno(), obj.getFec_nacimiento(), obj.getSexo(), obj.getCod_tipo_doc(),obj.getEstado_validacion()
							,obj.getFec_validacion(),obj.getRol_validacion(), obj.getDni(), obj.getId_persona());				
			}
			return seq;
		}
		
		public Number GetLastIdServicioCivil()
		{
			Number idServicioCivil=1;
			//try {			
				String sSQL = "select seq_id_servicio_civil.NEXTVAL as id_servicio_civil from dual"; 
				List<Map<String, Object>> lTGMaps = jdbcTemplate.queryForList(sSQL);			
				for(Map<String, Object> obj : lTGMaps){
					idServicioCivil = (Number)obj.get("id_servicio_civil");					
				}
			/*
			} catch (Exception e) {
				
			}			
			*/
			idServicioCivil= idServicioCivil.intValue();
			return idServicioCivil;
		}
		
		public void AddServicioCivil(ServicioCivil obj, Number idServicioCivil)		
		{
			String mensaje="";
			String clave="1234";
			//try
			//{
			// Generamos la clave de 4 digitos num�ricos
			clave = (String) this.jdbcTemplate.queryForObject("SELECT to_char(round(dbms_random.value(1000, 9999),0)) as clave FROM dual"
																, String.class, new Object[]{});
			
			String sql = "INSERT into SERVICIO_CIVIL(ID_RESPONSABLE,ID_SERVICIO_CIVIL, CORREO, TELEFONO, FEC_OBSERVACION, PLAZO" +
					", FEC_SUBSANACION, DETALLE_OBSERVACION, ESTADO_OBSERVACION, ID_EJECUTORA, COD_PROFESION" +
					", COD_REGIMEN, CLAVE, DETALLE_SUBSANACION, FEC_REGISTRO, COD_ESTADO, DNI, DNIPREVIO "+
					", JEFE, ID_PERSONA,ID_PUESTO_TIPO,ESTADO_EQUIPO,COD_ESTUDIOS,COD_GRADO_ACADEMICO "+
					", COD_EXPERIENCIA_SNIP,TITULO_UNIVERSITARIO,CORREO_ALTERNO "+
					", OTROSESTUDIOS, UNIVERSIDAD, ID_SISTEMA_ETAPA, COD_EXPERIENCIA_MATERIA " +
					", ID_UNIVERSIDAD, CODDEP, CODPRO, CODDIS, CERTIFICADO, CODCERTIFICADO, DIRECCION, COD_EXPERIENCIA_GENERAL " +
					", ID_TIPO_ESTUDIO " +
					" ) " +
			" VALUES(?,?,?,?,to_date(?,'DD/MM/YYYY'),?,to_date(?,'DD/MM/YYYY'),?,?,?,?,?,?,?,sysdate,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
			jdbcTemplate.update(sql, obj.getId_responsable(),idServicioCivil, obj.getCorreo(), obj.getTelefono(), obj.getFec_observacion() , obj.getPlazo()
								, obj.getFec_subsanacion(), obj.getDetalle_observacion(), obj.getEstado_observacion(), obj.getId_ejecutora(), obj.getCod_profesion()
								, obj.getCod_regimen(), clave, obj.getDetalle_subsanacion(), obj.getCod_estado(), obj.getDni(),obj.getDni(),obj.getJefe(),
								obj.getId_persona(), obj.getId_puesto_tipo(), obj.getEstado_equipo(),obj.getCod_estudios(),
								obj.getCod_grado_academico(),obj.getCod_experiencia_snip(),obj.getTitulo_universitario(),
								obj.getCorreo_alterno(),obj.getOtrosestudios(),obj.getUniversidad(),
								obj.getId_sistema_etapa(), obj.getCod_experiencia_materia()
								,obj.getId_universidad(), obj.getCoddepdir(), obj.getCodprodir(), obj.getCoddisdir(),
								obj.getCertificado(), obj.getCodcertificado(), obj.getDireccion(), 
								obj.getCod_experiencia_general(),obj.getId_tipo_estudio());
			
			sql = "INSERT into servicio_civil_ambito(ID_SERVICIO_CIVIL, ID_AMBITO) " +
					" VALUES(?,?) ";
					jdbcTemplate.update(sql, idServicioCivil, obj.getId_ambito());
			
			
			/*
			} catch (Exception e) {
				// TODO: handle exception
			}*/	
		}
		
		public String AgregarFuncionesSC(Number idServicioCivil, String listaFunciones, Number idAmbito)
		{
			String mensaje =""; 
			//try
			//{
			String sql = "DELETE from servicio_civil_actividad where id_servicio_civil = ? ";
			jdbcTemplate.update(sql,idServicioCivil);
			
			
			sql = "insert into servicio_civil_actividad (id_servicio_civil, id_actividad) " +
					" select "+idServicioCivil.toString()+" as id_sc, id_actividad " +
					" from actividad where id_funcion in ("+listaFunciones+")";
			jdbcTemplate.update(sql);
			/*}
			catch (Exception e) 
			{
				mensaje = e.getMessage();
				// TODO: handle exception
			}	*/
			return mensaje;
		}
		
		public String VerificarExisteDNI(String dni, Number idEjecutora)
		{
			String dato ="";
			//try {			
				// TODO: Confirmar si debe Verificar tipo y nro de documento
				/* @JZM-05022013 Arson
				 * Se anula la b�squeda de DNI por ejecutora. Solo se deja la validaci�n del DNI en el universo de datos por ambito activo
				 */
				//String sSQL = "select dni from servicio_civil where dni = '" + dni +"' and id_ejecutora = "+idEjecutora;
				String sSQL = "select s1.dni from servicio_civil s1 "+
								"join responsable_ejecutora_ambito s2 "+ 
								"on S2.ID_RESPONSABLE = S1.ID_RESPONSABLE "+
								"join (select id_ambito from ambito where id_sistema in (select id_sistema from sistema where ejecucion='1') ) s3 "+ 
								"on s3.id_ambito = s2.id_ambito "+
								"where s1.dni='" + dni + "'";
				List<Map<String, Object>> lTGMaps = jdbcTemplate.queryForList(sSQL);			
				for(Map<String, Object> obj : lTGMaps){
					dato = (String)obj.get("dni"); 
				}
			/*} catch (Exception e) {
				// TODO: handle exception
			}*/		
			
			return dato;
			
		}
		
		public String VerificarExisteOtroDNI(String dni, Number idAmbito, Number idServicioCivil)
		{
			String dato ="";
			//try {			
				// TODO: Confirmar si debe Verificar tipo y nro de documento
				//String sSQL = "select dni from servicio_civil where dni = '" + dni +"' and id_ejecutora = "+idEjecutora+" and id_servicio_civil <> "+idServicioCivil;
/*				String sSQL = "select s1.dni from servicio_civil s1 "+
								"join responsable_ejecutora_ambito s2 "+ 
								"on S2.ID_RESPONSABLE = S1.ID_RESPONSABLE "+
								"join (select id_ambito from ambito where id_sistema in (select id_sistema from sistema where ejecucion='1') ) s3 "+ 
								"on s3.id_ambito = s2.id_ambito "+
								"where s1.dni='" + dni + "'";*/
			String sSQL = "select s1.dni from servicio_civil s1 "+
			"join servicio_civil_ambito s2 "+ 
			"on S2.ID_SERVICIO_CIVIL = S1.ID_SERVICIO_CIVIL "+
			"join (select id_ambito from ambito where id_sistema in (select id_sistema from ambito where id_ambito="+idAmbito+") ) s3  "+ 
			"on s3.id_ambito = s2.id_ambito "+
			"where s1.dni='" + dni + "' and s1.id_servicio_civil <> "+idServicioCivil;
//				System.out.println("VerificarExisteOtroDNI");
//				System.out.println(sSQL);
				List<Map<String, Object>> lTGMaps = jdbcTemplate.queryForList(sSQL);			
				for(Map<String, Object> obj : lTGMaps){
					dato = (String)obj.get("dni"); 
				}
			/*} catch (Exception e) {
				// TODO: handle exception
			}*/		
			
			return dato;
			
		}
		
		public String VerificarExisteOtroCorreo(String correo, Number idAmbito, Number idServicioCivil)
		{
			String dato ="";
			//try {			
				// TODO: Confirmar si debe Verificar tipo y nro de documento
				//String sSQL = "select dni from servicio_civil where dni = '" + dni +"' and id_ejecutora = "+idEjecutora+" and id_servicio_civil <> "+idServicioCivil;
/*				String sSQL = "select s1.dni from servicio_civil s1 "+
								"join responsable_ejecutora_ambito s2 "+ 
								"on S2.ID_RESPONSABLE = S1.ID_RESPONSABLE "+
								"join (select id_ambito from ambito where id_sistema in (select id_sistema from sistema where ejecucion='1') ) s3 "+ 
								"on s3.id_ambito = s2.id_ambito "+
								"where s1.dni='" + dni + "'";*/
			String sSQL = "select s1.dni from servicio_civil s1 "+
			"join servicio_civil_ambito s2 "+ 
			"on S2.ID_SERVICIO_CIVIL = S1.ID_SERVICIO_CIVIL "+
			"join (select id_ambito from ambito where id_sistema in (select id_sistema from ambito where id_ambito="+idAmbito+") ) s3  "+ 
			"on s3.id_ambito = s2.id_ambito "+
			"where upper(s1.correo)=upper('" + correo + "')" + " and s1.id_servicio_civil <> "+idServicioCivil;
			//"where s1.dni='" + dni + "' and s1.id_servicio_civil <> "+idServicioCivil;
//				System.out.println("VerificarExisteOtroCorreo");
//				System.out.println(sSQL);
				List<Map<String, Object>> lTGMaps = jdbcTemplate.queryForList(sSQL);			
				for(Map<String, Object> obj : lTGMaps){
					dato = (String)obj.get("dni"); 
				}
			/*} catch (Exception e) {
				// TODO: handle exception
			}*/		
			
			return dato;
			
		}
		
		public void EliminarServicioCivil(ServicioCivil obj)
		{
			String sql = "call sp_del_servicio_civil(" + obj.getId_servicio_civil() + "," + obj.getId_ambito() + ")";
			try {
				jdbcTemplate.update(sql);
			} catch (DataAccessException e) {
				e.printStackTrace();
			}		
		}
		
		public String EditarServicioCivil(ServicioCivil obj)
		{
			String mensaje="";
			//try {	
				// Edit "Persona"
				//EditPersona(obj);
			obj.setId_persona(AddPersona(obj));
				// Edit "Servicio_Civil"
				EditServicioCivil(obj);
			/*}
			catch (Exception e) {
				// TODO: handle exception				
			}*/	
			return mensaje;
		}
		
		
		public void EditPersona(ServicioCivil obj)		
		{
			//try
			//{
			// Puede darse el problema de una persona para la cual de cambiar� el
			// numero de DNI
			
			
			String mensaje="";
			String sql = "UPDATE PERSONA SET NOMBRES=?, APELLIDO_PATERNO=? " +
					", APELLIDO_MATERNO=?, FEC_NACIMIENTO=to_date(?,'DD/MM/YYYY'), SEXO=?" +
					", COD_TIPO_DOC=?  WHERE DNI = ? and ID_PERSONA = ?";
			jdbcTemplate.update(sql, obj.getNombres(), obj.getApellido_paterno(), obj.getApellido_materno()
					, obj.getFec_nacimiento(), obj.getSexo(), obj.getCod_tipo_doc(), obj.getDni(), obj.getId_persona());
			/*} catch (Exception e) {
				// TODO: handle exception
			}*/	
		}

		
		public void EditServicioCivil(ServicioCivil obj)		
		{
			//try
			//{
			String sql = "update SERVICIO_CIVIL SET CORREO=?, TELEFONO=? " +
					", ID_EJECUTORA=?, COD_PROFESION=?" +
					", COD_REGIMEN=?, COD_ESTADO=?, DNI=? " +
					", JEFE =?, ID_PUESTO_TIPO = ?, ESTADO_EQUIPO = ?"+
					", COD_ESTUDIOS=?, COD_EXPERIENCIA_SNIP=?, COD_GRADO_ACADEMICO=?"+
					", TITULO_UNIVERSITARIO=?, CORREO_ALTERNO=?"+
					", OTROSESTUDIOS=?, UNIVERSIDAD=?"+
					", ID_PERSONA = ? " +
					", ID_SISTEMA_ETAPA = ? , COD_EXPERIENCIA_MATERIA = ? " +
					", ID_UNIVERSIDAD = ?, CODDEP = ?, CODPRO = ?, CODDIS = ? "+
					", CERTIFICADO = ?, CODCERTIFICADO = ?, DIRECCION = ?, COD_EXPERIENCIA_GENERAL = ?  " +
					", ID_TIPO_ESTUDIO = ? " +
					" WHERE ID_SERVICIO_CIVIL = ? ";
			jdbcTemplate.update(sql, obj.getCorreo(), obj.getTelefono() 
								, obj.getId_ejecutora(), obj.getCod_profesion()
								, obj.getCod_regimen(), obj.getCod_estado(), obj.getDni()
								, obj.getJefe(), obj.getId_puesto_tipo(), obj.getEstado_equipo()
								, obj.getCod_estudios(),obj.getCod_experiencia_snip(), obj.getCod_grado_academico()
								,obj.getTitulo_universitario(), obj.getCorreo_alterno()
								, obj.getOtrosestudios(),obj.getUniversidad()
								, obj.getId_persona()
								, obj.getId_sistema_etapa(), obj.getCod_experiencia_materia()
								, obj.getId_universidad(), obj.getCoddepdir(), obj.getCodprodir(), obj.getCoddisdir()
								, obj.getCertificado(), obj.getCodcertificado(), obj.getDireccion(), obj.getCod_experiencia_general()
								,obj.getId_tipo_estudio()
								, obj.getId_servicio_civil());
			
			/*} catch (Exception e) {
				// TODO: handle exception
			}*/	
		}
		
		/*
			String sql = "update SERVICIO_CIVIL SET CORREO=?, TELEFONO=? " +
					", ID_EJECUTORA=?, COD_PROFESION=?" +
					", COD_REGIMEN=?, COD_ESTADO=?, DNI=? " +
					", JEFE =?, ID_PUESTO_TIPO = ?, ESTADO_EQUIPO = ? WHERE ID_SERVICIO_CIVIL = ? ";
			jdbcTemplate.update(sql, obj.getCorreo(), obj.getTelefono() 
								, obj.getId_ejecutora(), obj.getCod_profesion()
								, obj.getCod_regimen(), obj.getCod_estado(), obj.getDni()
								, obj.getJefe(), obj.getId_puesto_tipo(), obj.getEstado_equipo(), obj.getId_servicio_civil());
		
		*/
		
		public List ObtenerActividades(Number idFuncion)
		{
			List actividades = null;
			
			String sql="select id_actividad, descripcion " +
					" from actividad where id_funcion = "+idFuncion.toString()+" order by descripcion";		
			
			List<Map<String, Object>> lEntidadMaps = jdbcTemplate.queryForList(sql);
			
			for(Map<String, Object> obj : lEntidadMaps){
				Actividad g = new Actividad();
				g.setId_actividad((Number)obj.get("id_actividad"));
				g.setDescripcion((String)obj.get("descripcion"));			
				if (actividades == null) actividades = new ArrayList();
				actividades.add(g);			
				g = null;			
			}
			return actividades;			
		}
		
		public String ObtenerDescEtapaFuncion(Number idFuncion)
		{
			String dato ="";
			//try {			
				// TODO: Confirmar si debe Verificar tipo y nro de documento
				String sSQL = "select (select trim(descripcion) from etapa where id_etapa = fun.id_etapa)  " +
						" 	|| '|' || trim(descripcion) as desc_etapa_funcion " +
						" from funcion fun where id_funcion = " + idFuncion.toString() ;
				List<Map<String, Object>> lTGMaps = jdbcTemplate.queryForList(sSQL);	
				//System.out.println(sSQL);
				for(Map<String, Object> obj : lTGMaps){
					dato = (String)obj.get("desc_etapa_funcion"); 
			}
			/*} catch (Exception e) {
				// TODO: handle exception
			}*/	
			
			return dato;
		}
		
		public LinkedList<NivelGobierno> ObtenerNivelGobiernos()
		{
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList("select ID_NIVEL_GOBIERNO, DESCRIPCION FROM NIVEL_GOBIERNO where estado = 1");
			LinkedList<NivelGobierno> obj = new LinkedList<NivelGobierno>();
			for(Map<String, Object> rec : lSectorMaps)
			{
				NivelGobierno s = new NivelGobierno();
				s.setId_nivel_gobierno((Number)rec.get("ID_NIVEL_GOBIERNO"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				obj.add(s);
			}
			return obj;		
		}
		
		public LinkedList<Sistema> ObtenerSistemas()
		{
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList("select ID_SISTEMA, DESCRIPCION, ACRONIMO, COD_ETAPA " +
						" FROM SISTEMA ");
			LinkedList<Sistema> obj = new LinkedList<Sistema>();
			for(Map<String, Object> rec : lSectorMaps)
			{
				Sistema s = new Sistema();
				s.setId_sistema((Number)rec.get("ID_SISTEMA"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				s.setAcronimo((String)rec.get("ACRONIMO"));
				s.setCod_etapa((String)rec.get("COD_ETAPA"));
				obj.add(s);
			}
			return obj;
		}
		
		public LinkedList<EstadoInscripcion> ObtenerEstadoInscripcion() { 
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList("select codigo, trim(descripcion) as desc_estado " +
						" from codigo where grupo = 'EINS' and estado = 1 order by orden");
			LinkedList<EstadoInscripcion> obj = new LinkedList<EstadoInscripcion>();
			for(Map<String, Object> rec : lSectorMaps){
				EstadoInscripcion s = new EstadoInscripcion();
				s.setCod_estado((String)rec.get("codigo"));
				s.setDesc_estado((String)rec.get("desc_estado"));			
				obj.add(s);
			}
			return obj;
		}
		
		public LinkedList<Ambito> ObtenerAmbito()
		{
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList("SELECT ID_AMBITO, DESCRIPCION FROM AMBITO");
			LinkedList<Ambito> obj = new LinkedList<Ambito>();
			for(Map<String, Object> rec : lSectorMaps){
				Ambito s = new Ambito();
				s.setId_ambito((Number)rec.get("ID_AMBITO"));
				s.setId_sistema((Number)rec.get("ID_SISTEMA"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				obj.add(s);
			}
			return obj;
		}

		public LinkedList<Departamento> ObtenerDepartamentos() {
			// select coddep, descripcion from censo.ubigeo where  codpro = '00' and coddis = '00'
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList("select coddep, descripcion from ubigeo where " +
						" codpro = '00' and coddis = '00' and estado = '1' order by descripcion ");
			LinkedList<Departamento> obj = new LinkedList<Departamento>();
			for(Map<String, Object> rec : lSectorMaps){
				Departamento s = new Departamento();
				s.setCodDep((String)rec.get("CODDEP"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				obj.add(s);
			}
			return obj;
		}

		public LinkedList<Distrito> ObtenerDistritos(String coddep, String codpro) {
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList("select coddis, descripcion from ubigeo where +" +
						" coddep = '" + coddep + "' and codpro = '" + codpro  + "' and coddis <> '00' and estado = '1' order by descripcion ");
			LinkedList<Distrito> obj = new LinkedList<Distrito>();
			for(Map<String, Object> rec : lSectorMaps){
				Distrito s = new Distrito();
				s.setCodDis((String)rec.get("CODDIS"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				obj.add(s);
			}
			return obj;
			
		}

		public LinkedList<Ejecutora> ObtenerEjecutoras(Integer idPliego) {
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList("select id_ejecutora, descripcion from ejecutora WHERE " +
						" id_pliego = " + idPliego + " and estado = '1' order by descripcion");
			LinkedList<Ejecutora> obj = new LinkedList<Ejecutora>();
			for(Map<String, Object> rec : lSectorMaps){
				Ejecutora s = new Ejecutora();
				s.setId_ejecutora((Number)rec.get("ID_EJECUTORA"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				obj.add(s);
			}
			return obj;
		}

		public LinkedList<Pliego> ObtenerPliegos(Integer idSector) {
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList("select id_pliego, descripcion from pliego WHERE " +
						" id_sector = " + idSector + " and estado = '1' order by descripcion");
			LinkedList<Pliego> obj = new LinkedList<Pliego>();
			for(Map<String, Object> rec : lSectorMaps){
				Pliego s = new Pliego();
				s.setId_pliego((Number)rec.get("ID_PLIEGO"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				obj.add(s);
			}
			return obj;
		}

		public LinkedList<Provincia> ObtenerProvincias(String coddep) {
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList("select codpro, descripcion from ubigeo where +" +
						" coddep = '" + coddep + "' and coddis = '00' and codpro <> '00' and estado = '1' order by descripcion");
			LinkedList<Provincia> obj = new LinkedList<Provincia>();
			for(Map<String, Object> rec : lSectorMaps){
				Provincia s = new Provincia();
				s.setCodpro((String)rec.get("CODPRO"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				obj.add(s);
			}
			return obj;
		}

		public LinkedList<Sector> ObtenerSectors() {
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList("select id_sector, descripcion from sector where " +
						"  estado = '1' order by descripcion");
			LinkedList<Sector> obj = new LinkedList<Sector>();
			for(Map<String, Object> rec : lSectorMaps){
				Sector s = new Sector();
				s.setId_sector((Number)rec.get("ID_SECTOR"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				obj.add(s);
			}
			return obj;
		}

		public LinkedList<Ambito> ObtenerAmbito(Number idSistema) {
			List<Map<String, Object>> lSectorMaps = jdbcTemplate.queryForList("SELECT ID_SISTEMA, ID_AMBITO, " +
					"DESCRIPCION, ESTADO FROM AMBITO WHERE ID_SISTEMA = " + idSistema + " AND ESTADO = '1'");
			LinkedList<Ambito> obj = new LinkedList<Ambito>();
			for(Map<String, Object> rec : lSectorMaps){
				Ambito s = new Ambito();
				s.setId_sistema((Number)rec.get("ID_SISTEMA"));
				s.setId_ambito((Number)rec.get("ID_AMBITO"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));
				s.setEstado((String)rec.get("ESTADO"));
				obj.add(s);
			}
			return obj;
		}

		public LinkedList<Sistema> ObtenerSistema() {
			List<Map<String, Object>> lSectorMaps = jdbcTemplate.queryForList("SELECT ID_SISTEMA, DESCRIPCION," +
					" ACRONIMO, COD_ETAPA, CON_AMBITOS FROM SISTEMA WHERE COD_ETAPA <> 'FINA' ");
			LinkedList<Sistema> obj = new LinkedList<Sistema>();
			for(Map<String, Object> rec : lSectorMaps){
				Sistema s = new Sistema();
				s.setId_sistema((Number)rec.get("ID_SISTEMA"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));
				s.setAcronimo((String)rec.get("ACRONIMO"));
				s.setCon_ambitos((String)rec.get("CON_AMBITOS"));
				obj.add(s);
			}
			return obj;
		}
		
		public List<Diagnostico> ObtenerDiagnosticos()
		{
			List<Map<String, Object>> lSectorMaps = jdbcTemplate.queryForList("select id_diagnostico, " +
					" descripcion, to_char(fec_prueba, 'dd/mm/yyy') as fec_prueba, to_char(fec_ini_habilitacion,'dd/mm/yyy') as fec_ini_habilitacion, " +
					" to_char(fec_fin_habilitacion, 'dd/mm/yyy') as fec_fin_habilitacion, to_char(fec_ini_registro, 'dd/mm/yyy') as fec_ini_registro, to_char(fec_fin_registro, 'dd/mm/yyy') as fec_fin_registro, estado " +
					" from diagnostico where estado = 1");
				LinkedList<Diagnostico> obj = new LinkedList<Diagnostico>();
			for(Map<String, Object> rec : lSectorMaps){
				Diagnostico s = new Diagnostico();
				s.setId_diagnostico((Number)rec.get("ID_DIAGNOSTICO"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));
				s.setFec_prueba((String)rec.get("FEC_PRUEBA"));
				s.setFec_ini_habilitacion((String)rec.get("FEC_INI_HABILITACION"));
				s.setFec_fin_habilitacion((String)rec.get("FEC_FIN_HABILITACION"));
				s.setFec_ini_registro((String)rec.get("FEC_INI_REGISTRO"));
				s.setFec_fin_registro((String)rec.get("FEC_FIC_REGISTRO"));
				s.setEstado((String)rec.get("ESTADO"));
				obj.add(s);
			}
			return obj;
		}
		
		public String ObtenerRptQueryServicioCivil(ServicioCivilBusqueda obj,
				String estadoRegistro) {
			
			String sql = "select sc.id_servicio_civil, case when nvl(sc.jefe,'0') = '1' then 'SI' else 'NO' end as jefe, sc.dni, p.apellido_paterno || ' ' || p.apellido_materno || ' ' || p.nombres as apellidosNombres," +
					" e.descripcion as DESCRIPCIONEJECUTORA, " +
					" case when nvl(p.estado_validacion,'0') = '0' then 'NO' else 'SI' end as v, " +
					" case when nvl(sc.estado_observacion, '0') = '0' then 'NO' else 'SI' end as h, " +
					" sc.cod_estado " + 
					" from servicio_civil sc join persona p on sc.dni = p.dni and sc.id_persona = p.id_persona " +
					" join ejecutora e on e.id_ejecutora = sc.id_ejecutora " +
					" left join pliego pli on pli.id_pliego = e.id_pliego left join sector sec on sec.id_sector = pli.id_sector " +
					" join servicio_civil_ambito sca on sca.id_servicio_civil = sc.id_servicio_civil " +
					" join ambito am on am.id_ambito = sca.id_ambito join sistema s on s.id_sistema = am.id_sistema " +
					" where 1 =  1 ";
			if (obj.getApellidos_paterno() != null)
				if (!"".equals(obj.getApellidos_paterno()))
					sql += " and p.apellido_paterno like ('%" + obj.getApellidos_paterno() + "%')";
			if (obj.getApellidos_materno() != null)
				if (!"".equals(obj.getApellidos_materno()))
					sql += " and p.apellido_materno like ('%" + obj.getApellidos_materno() + "%')";
			if (obj.getNombres() != null)
				if (!"".equals(obj.getNombres()))
					sql += " and p.nombres like ('%" + obj.getNombres() + "%')";
			if (obj.getDni() != null)
				if (!"".equals(obj.getDni()))
					sql += " and p.dni = '" + obj.getDni() + "'";
			if (obj.getIdNivelGobierno() != null)
				if (obj.getIdNivelGobierno().intValue() != 0)
					sql += " and e.id_nivel_gobierno = " + obj.getIdNivelGobierno()+ " ";
			if (obj.getIdSector() != null)
				if (obj.getIdSector().intValue() != 0)
					sql += " and sec.id_sector = " + obj.getIdSector() + " ";
			if (obj.getIdPliego() != null)
				if (obj.getIdPliego().intValue() != 0)
					sql += " and pli.id_pliego = " + obj.getIdPliego() + " ";
			if (obj.getIdEjecutora() != null)
				if (obj.getIdEjecutora().intValue() != 0)
					sql += " and e.id_ejecutora = " + obj.getIdEjecutora() + " ";
			if (obj.getCoddep() != null)
				if (!"0".equals(obj.getCoddep()))
					sql += " and substr(e.ubigeo, 1, 2) = '" + obj.getCoddep() + "' ";
			if (obj.getCodpro() != null)
				if (!"0".equals(obj.getCodpro()))
					sql += " and substr(e.ubigeo, 3, 2) = '" + obj.getCodpro() + "' ";
			if (obj.getCoddep() != null)
				if (!"0".equals(obj.getCoddep()))
					sql += " and substr(e.ubigeo, 5, 2) = '" + obj.getCoddep() + "' ";
			if (estadoRegistro != null)
			{
				if (estadoRegistro.equals("REGI"))
				{
					sql += " and nvl(sc.estado_observacion,'0') = '0' ";
					sql += " and nvl(p.estado_validacion, '0') = '0' ";
				}
				if (estadoRegistro.equals("VALI"))
				{
					sql += " and nvl(p.estado_validacion, '0') = '1' ";
				}
				if (estadoRegistro.equals("OBS"))
				{
					sql += " and nvl(sc.estado_observacion,'0') = '1' ";
				}
			}
			else
			{
				sql += " and nvl(sc.estado_observacion,'0') = '0' ";
				sql += " and nvl(p.estado_validacion, '0') = '0' ";
			}
			if (obj.getIdSistema() != null)
				if (obj.getIdSistema() != 0)
					sql += " and s.id_sistema = " + obj.getIdSistema() + " ";
			if (obj.getIdAmbito() != null)
				if (obj.getIdAmbito() != 0)
					sql += " and am.id_ambito = " + obj.getIdAmbito() + " ";
			
			sql += " and not exists (select 1 from responsable where dni = sc.dni and id_persona = sc.id_persona)";
			sql += " order by sc.fec_registro desc  ";
			
			return sql;
		}
		///
		//JUANK
		public List ObtenerRptServicioCivil(ServicioCivilBusqueda obj,
				String estadoRegistro) {
			List listaRptServicioCivil = null;
			
			String sql = "select sc.id_servicio_civil, case when nvl(sc.jefe,'0') = '1' then 'SI' else 'NO' end as jefe, sc.dni, p.apellido_paterno || ' ' || p.apellido_materno || ' ' || p.nombres as apellidosNombres," +
					" e.descripcion as DESCRIPCIONEJECUTORA, " +
					" case when nvl(p.estado_validacion,'0') = '0' then 'NO' else 'SI' end as v, " +
					" case when nvl(sc.estado_observacion, '0') = '0' then 'NO' else 'SI' end as h, " +
					" sc.cod_estado " + 
					" from servicio_civil sc join persona p on sc.dni = p.dni and sc.id_persona = p.id_persona " +
					" join ejecutora e on e.id_ejecutora = sc.id_ejecutora " +
					" left join pliego pli on pli.id_pliego = e.id_pliego left join sector sec on sec.id_sector = pli.id_sector " +
					" join servicio_civil_ambito sca on sca.id_servicio_civil = sc.id_servicio_civil " +
					" join ambito am on am.id_ambito = sca.id_ambito join sistema s on s.id_sistema = am.id_sistema " +
					" where 1 =  1 ";
			if (obj.getApellidos_paterno() != null)
				if (!"".equals(obj.getApellidos_paterno()))
					sql += " and p.apellido_paterno like ('%" + obj.getApellidos_paterno() + "%')";
			if (obj.getApellidos_materno() != null)
				if (!"".equals(obj.getApellidos_materno()))
					sql += " and p.apellido_materno like ('%" + obj.getApellidos_materno() + "%')";
			if (obj.getNombres() != null)
				if (!"".equals(obj.getNombres()))
					sql += " and p.nombres like ('%" + obj.getNombres() + "%')";
			if (obj.getDni() != null)
				if (!"".equals(obj.getDni()))
					sql += " and p.dni = '" + obj.getDni() + "'";
			if (obj.getIdNivelGobierno() != null)
				if (obj.getIdNivelGobierno().intValue() != 0)
					sql += " and e.id_nivel_gobierno = " + obj.getIdNivelGobierno()+ " ";
			if (obj.getIdSector() != null)
				if (obj.getIdSector().intValue() != 0)
					sql += " and sec.id_sector = " + obj.getIdSector() + " ";
			if (obj.getIdPliego() != null)
				if (obj.getIdPliego().intValue() != 0)
					sql += " and pli.id_pliego = " + obj.getIdPliego() + " ";
			if (obj.getIdEjecutora() != null)
				if (obj.getIdEjecutora().intValue() != 0)
					sql += " and e.id_ejecutora = " + obj.getIdEjecutora() + " ";
			if (obj.getCoddep() != null)
				if (!"0".equals(obj.getCoddep()))
					sql += " and substr(e.ubigeo, 1, 2) = '" + obj.getCoddep() + "' ";
			if (obj.getCodpro() != null)
				if (!"0".equals(obj.getCodpro()))
					sql += " and substr(e.ubigeo, 3, 2) = '" + obj.getCodpro() + "' ";
			if (obj.getCoddep() != null)
				if (!"0".equals(obj.getCoddep()))
					sql += " and substr(e.ubigeo, 5, 2) = '" + obj.getCoddep() + "' ";
			if (estadoRegistro != null)
			{
				if (estadoRegistro.equals("REGI"))
				{
					sql += " and nvl(sc.estado_observacion,'0') = '0' ";
					sql += " and nvl(p.estado_validacion, '0') = '0' ";
				}
				if (estadoRegistro.equals("VALI"))
				{
					sql += " and nvl(p.estado_validacion, '0') = '1' ";
				}
				if (estadoRegistro.equals("OBS"))
				{
					sql += " and nvl(sc.estado_observacion,'0') = '1' ";
				}
			}
			else
			{
				sql += " and nvl(sc.estado_observacion,'0') = '0' ";
				sql += " and nvl(p.estado_validacion, '0') = '0' ";
			}
			if (obj.getIdSistema() != null)
				if (obj.getIdSistema() != 0)
					sql += " and s.id_sistema = " + obj.getIdSistema() + " ";
			if (obj.getIdAmbito() != null)
				if (obj.getIdAmbito() != 0)
					sql += " and am.id_ambito = " + obj.getIdAmbito() + " ";
			
			sql += " and not exists (select 1 from responsable where dni = sc.dni and id_persona = sc.id_persona)";
			sql += " order by sc.fec_registro desc  ";
			List<Map<String, Object>> lEntidadMaps = jdbcTemplate.queryForList(sql);
			
			for(Map<String, Object> rec : lEntidadMaps){
				RptServicioCivilBusqueda e = new RptServicioCivilBusqueda();
				e.setId_servicio_civil((Number)rec.get("ID_SERVICIO_CIVIL"));
				e.setJefe((String)rec.get("JEFE"));
				e.setDni((String)rec.get("DNI"));
				e.setApellidosNombres((String)rec.get("APELLIDOSNOMBRES"));
				e.setDescripcionEjecutora((String)rec.get("DESCRIPCIONEJECUTORA"));
				e.setH((String)rec.get("H"));
				e.setEstado((String)rec.get("COD_ESTADO"));
				e.setV ((String)rec.get("V"));
				if (listaRptServicioCivil == null) listaRptServicioCivil = new ArrayList();
				listaRptServicioCivil.add(e);
				e = null;
			}
			return listaRptServicioCivil;
		}
		//JUANK
		////
		public String ObtenerRptQueryResponsables(ResponsableBusqueda obj,
				String estadoRegistro) {
			
				String sql = "select r.id_responsable, r.dni, p.apellido_paterno || ' ' || p.apellido_materno || ' ' || p.nombres as apellidosNombres, "+
					" e.descripcion as DESCRIPCIONEJECUTORA, case when nvl(p.estado_validacion,'0') = '0' then 'NO' else 'SI' end as v, " +
					" case when nvl(rea.estado_registro, '0') = '0' then 'NO' else 'SI' end as h " +
					" from responsable r join persona p on r.dni = p.dni and r.id_persona = p.id_persona " +
					" join responsable_ejecutora_ambito rea on rea.id_responsable = r.id_responsable " +
					" join ejecutora e on e.id_ejecutora = rea.id_ejecutora " +
					" left join pliego pli on pli.id_pliego = e.id_pliego left join sector sec on sec.id_sector = pli.id_sector " +
					" join ambito am on am.id_ambito = rea.id_ambito join sistema s on s.id_sistema = am.id_sistema " +
					" where 1 =  1 and rea.estado = '1' and r.cod_rol_responsable = 'RESP' ";
			
			if (obj.getApellidos_paterno() != null)
				if (!"".equals(obj.getApellidos_paterno()))
					sql += " and p.apellido_paterno like ('%" + obj.getApellidos_paterno() + "%')";
			if (obj.getApellidos_materno() != null)
				if (!"".equals(obj.getApellidos_materno()))
					sql += " and p.apellido_materno like ('%" + obj.getApellidos_materno() + "%')";
			if (obj.getNombres() != null)
				if (!"".equals(obj.getNombres()))
					sql += " and p.nombres like ('%" + obj.getNombres() + "%')";
			if (obj.getDni() != null)
				if (!"".equals(obj.getDni()))
					sql += " and p.dni = '" + obj.getDni() + "'";
			if (obj.getIdNivelGobierno() != null)
				if (obj.getIdNivelGobierno().intValue() != 0)
					sql += " and e.id_nivel_gobierno = " + obj.getIdNivelGobierno()+ " ";
			if (obj.getIdSector() != null)
				if (obj.getIdSector().intValue() != 0)
					sql += " and sec.id_sector = " + obj.getIdSector() + " ";
			if (obj.getIdPliego() != null)
				if (obj.getIdPliego().intValue() != 0)
					sql += " and pli.id_pliego = " + obj.getIdPliego() + " ";
			if (obj.getIdEjecutora() != null)
				if (obj.getIdEjecutora().intValue() != 0)
					sql += " and e.id_ejecutora = " + obj.getIdEjecutora() + " ";
			if (obj.getCoddep() != null)
				if (!"0".equals(obj.getCoddep()))
					sql += " and substr(e.ubigeo, 1, 2) = '" + obj.getCoddep() + "' ";
			if (obj.getCodpro() != null)
				if (!"0".equals(obj.getCodpro()))
					sql += " and substr(e.ubigeo, 3, 2) = '" + obj.getCodpro() + "' ";
			if (obj.getCoddep() != null)
				if (!"0".equals(obj.getCoddep()))
					sql += " and substr(e.ubigeo, 5, 2) = '" + obj.getCoddep() + "' ";
			if (obj.getCodEstadosResponsable() != null)
			{
				estadoRegistro = obj.getCodEstadosResponsable();
				if (estadoRegistro.equals("REGI"))
				{
					//sql += " and nvl(rea.estado_registro,'0') = '0' ";
					//sql += " and nvl(p.estado_validacion, '0') = '0' ";
					sql += " and (nvl(rea.estado_registro,'0') = '0' ";
					sql += " or nvl(p.estado_validacion, '0') = '0') ";
				}
				if (estadoRegistro.equals("VALI"))
				{
					sql += " and nvl(p.estado_validacion, '0') = '1' ";
				}
				if (estadoRegistro.equals("HABI"))
				{
					sql += " and nvl(rea.estado_registro,'0') = '1' ";
				}
				if (estadoRegistro.equals("TODO") || estadoRegistro.equals("0"))
				{
					sql += " and nvl(rea.estado_registro,'0') in ('0','1','2','3') ";
				}
			}
			else
			{
				sql += " and (nvl(rea.estado_registro,'0') = '0' ";
				sql += " or nvl(p.estado_validacion, '0') = '0') ";
			}
			if (obj.getCodEstadosPlazo() != null)
			{
				String estadoPlazo = obj.getCodEstadosPlazo();
				if (estadoPlazo.equals("CUMP"))
				{
					sql += " and rea.fec_cierre < to_date(to_char(sysdate - 1, 'dd/mm/yyyy') || ' 23:59','dd/mm/yyyy HH24:MI') ";
				}
				if (estadoPlazo.equals("VIGE"))
				{
					sql += " and rea.fec_cierre >= to_date(to_char(sysdate - 1, 'dd/mm/yyyy') || ' 23:59','dd/mm/yyyy HH24:MI') ";
				}
			
			}
			if (obj.getCodEstadosInscritos() != null)
			{
				String estadoPlazo = obj.getCodEstadosInscritos();
				if (estadoPlazo.equals("CPAR"))
				{
					sql += " and (select count(*) from servicio_civil sc where sc.id_ejecutora = rea.id_ejecutora and sc.jefe = 0) > 0 ";
				}
				if (estadoPlazo.equals("SPAR"))
				{
					sql += " and (select count(*) from servicio_civil sc where sc.id_ejecutora = rea.id_ejecutora and sc.jefe = 0) = 0 ";
				}
			
			}
			if (obj.getIdSistema() != null)
				if (obj.getIdSistema() != 0)
					sql += " and s.id_sistema = " + obj.getIdSistema() + " ";
			if (obj.getIdAmbito() != null)
				if (obj.getIdAmbito() != 0)
					sql += " and am.id_ambito = " + obj.getIdAmbito() + " ";
			
			sql += " order by rea.fec_asignacion desc  ";
			
			return sql;
		}
		
		public List ObtenerRptResponsables(ResponsableBusqueda obj,
				String estadoRegistro) {
			List listaRptResponsables = null;
			//if(estadoRegistro.equals("1") || estadoRegistro.equals("2")){
			String sql = "select r.id_responsable, r.dni, p.apellido_paterno || ' ' || p.apellido_materno || ' ' || p.nombres as apellidosNombres, "+
				" e.descripcion as DESCRIPCIONEJECUTORA, case when nvl(p.estado_validacion,'0') = '0' then 'NO' else 'SI' end as v, " +
				" case when nvl(rea.estado_registro, '0') = '0' then 'NO' else 'SI' end as h " +
				" from responsable r join persona p on r.dni = p.dni and r.id_persona = p.id_persona " +
				" join responsable_ejecutora_ambito rea on rea.id_responsable = r.id_responsable " +
				" join ejecutora e on e.id_ejecutora = rea.id_ejecutora " +
				" left join pliego pli on pli.id_pliego = e.id_pliego left join sector sec on sec.id_sector = pli.id_sector " +
				" join ambito am on am.id_ambito = rea.id_ambito join sistema s on s.id_sistema = am.id_sistema " +
				" where 1 =  1 and rea.estado = '1' and r.cod_rol_responsable = 'RESP' ";
			
			if (obj.getApellidos_paterno() != null)
				if (!"".equals(obj.getApellidos_paterno()))
					sql += " and p.apellido_paterno like ('%" + obj.getApellidos_paterno() + "%')";
			if (obj.getApellidos_materno() != null)
				if (!"".equals(obj.getApellidos_materno()))
					sql += " and p.apellido_materno like ('%" + obj.getApellidos_materno() + "%')";
			if (obj.getNombres() != null)
				if (!"".equals(obj.getNombres()))
					sql += " and p.nombres like ('%" + obj.getNombres() + "%')";
			if (obj.getDni() != null)
				if (!"".equals(obj.getDni()))
					sql += " and p.dni = '" + obj.getDni() + "'";
			if (obj.getIdNivelGobierno() != null)
				if (obj.getIdNivelGobierno().intValue() != 0)
					sql += " and e.id_nivel_gobierno = " + obj.getIdNivelGobierno()+ " ";
			if (obj.getIdSector() != null)
				if (obj.getIdSector().intValue() != 0)
					sql += " and sec.id_sector = " + obj.getIdSector() + " ";
			if (obj.getIdPliego() != null)
				if (obj.getIdPliego().intValue() != 0)
					sql += " and pli.id_pliego = " + obj.getIdPliego() + " ";
			if (obj.getIdEjecutora() != null)
				if (obj.getIdEjecutora().intValue() != 0)
					sql += " and e.id_ejecutora = " + obj.getIdEjecutora() + " ";
			if (obj.getCoddep() != null)
				if (!"0".equals(obj.getCoddep()))
					sql += " and substr(e.ubigeo, 1, 2) = '" + obj.getCoddep() + "' ";
			if (obj.getCodpro() != null)
				if (!"0".equals(obj.getCodpro()))
					sql += " and substr(e.ubigeo, 3, 2) = '" + obj.getCodpro() + "' ";
			if (obj.getCoddep() != null)
				if (!"0".equals(obj.getCoddep()))
					sql += " and substr(e.ubigeo, 5, 2) = '" + obj.getCoddep() + "' ";
			if (obj.getCodEstadosResponsable() != null)
			{
				estadoRegistro = obj.getCodEstadosResponsable();
				if (estadoRegistro.equals("REGI"))
				{
					//sql += " and nvl(rea.estado_registro,'0') = '0' ";
					//sql += " and nvl(p.estado_validacion, '0') = '0' ";
					sql += " and (nvl(rea.estado_registro,'0') = '0' ";
					sql += " or nvl(p.estado_validacion, '0') = '0') ";
				}
				if (estadoRegistro.equals("VALI"))
				{
					sql += " and nvl(p.estado_validacion, '0') = '1' ";
				}
				if (estadoRegistro.equals("HABI"))
				{
					sql += " and nvl(rea.estado_registro,'0') = '1' ";
				}
				if (estadoRegistro.equals("TODO") || estadoRegistro.equals("0"))
				{
					sql += " and nvl(rea.estado_registro,'0') in ('0','1','2','3') ";
				}
			}
			else
			{
				sql += " and (nvl(rea.estado_registro,'0') = '0' ";
				sql += " or nvl(p.estado_validacion, '0') = '0') ";
			}
			if (obj.getCodEstadosPlazo() != null)
			{
				String estadoPlazo = obj.getCodEstadosPlazo();
				if (estadoPlazo.equals("CUMP"))
				{
					sql += " and rea.fec_cierre < to_date(to_char(sysdate - 1, 'dd/mm/yyyy') || ' 23:59','dd/mm/yyyy HH24:MI') ";
				}
				if (estadoPlazo.equals("VIGE"))
				{
					sql += " and rea.fec_cierre >= to_date(to_char(sysdate - 1, 'dd/mm/yyyy') || ' 23:59','dd/mm/yyyy HH24:MI') ";
				}
			
			}
			if (obj.getCodEstadosInscritos() != null)
			{
				String estadoPlazo = obj.getCodEstadosInscritos();
				if (estadoPlazo.equals("CPAR"))
				{
					sql += " and (select count(*) from servicio_civil sc where sc.id_ejecutora = rea.id_ejecutora and sc.jefe = 0) > 0 ";
				}
				if (estadoPlazo.equals("SPAR"))
				{
					sql += " and (select count(*) from servicio_civil sc where sc.id_ejecutora = rea.id_ejecutora and sc.jefe = 0) = 0 ";
				}
			
			}
			if (obj.getIdSistema() != null)
				if (obj.getIdSistema() != 0)
					sql += " and s.id_sistema = " + obj.getIdSistema() + " ";
			if (obj.getIdAmbito() != null)
				if (obj.getIdAmbito() != 0)
					sql += " and am.id_ambito = " + obj.getIdAmbito() + " ";
			
			sql += " order by rea.fec_asignacion desc  ";
			List<Map<String, Object>> lEntidadMaps = jdbcTemplate.queryForList(sql);
			
			for(Map<String, Object> rec : lEntidadMaps){
				RptResponsableBusqueda e = new RptResponsableBusqueda();
				e.setId_responsable((Number)rec.get("ID_RESPONSABLE"));
				e.setDni((String)rec.get("DNI"));
				e.setApellidosNombres((String)rec.get("APELLIDOSNOMBRES"));
				e.setDescripcionEjecutora((String)rec.get("DESCRIPCIONEJECUTORA"));
				e.setH((String)rec.get("H"));
				e.setV ((String)rec.get("V"));
				if (listaRptResponsables == null) listaRptResponsables = new ArrayList();
				listaRptResponsables.add(e);
				e = null;
			}
			return listaRptResponsables;
		}
		//
		
		public List<Number> ObtenerIdFuncionesSC(Number idServicioCivil){
			String sql = "Select f.id_funcion "
				+" ,count(*) as nro_actividad,sum(case when sca.id_actividad is not null then 1 else 0 end) as nro_actividad_sel "
				+" from funcion f join actividad a on f.id_funcion = a.id_funcion "
				+" left join servicio_civil_actividad sca on a.id_actividad = sca.id_actividad and sca.id_servicio_civil = ?"
				+" group by f.id_funcion";
//			System.out.println("ObtenerIdFuncionesSC=====================================");
//			System.out.println(sql);
			List<Map<String, Object>> lSectorMaps = jdbcTemplate.queryForList(sql,idServicioCivil);
			List<Number> obj = new ArrayList<Number>();
			for(Map<String, Object> rec : lSectorMaps){
				Number id_funcion =(Number)rec.get("ID_FUNCION");
				Number nro_act_sel = (Number)rec.get("NRO_ACTIVIDAD_SEL");
				if (nro_act_sel.intValue() > 0)
					obj.add(id_funcion);
			}
			return obj;			
		}

		public ResponsableBusqueda ObtenerResponsable(Integer idResponsable) {
			ResponsableBusqueda  obj = null;
			String sql = "";
			sql = "select r.id_responsable, r.dni, p.apellido_paterno, p.apellido_materno, p.nombres," +
				" e.descripcion as DESCRIPCIONEJECUTORA, nvl(to_char(rea.fec_habilitacion, 'dd/mm/yyyy'),'') as FECHABILITACION, " +
				" p.cod_tipo_doc, r.correo, p.sexo, r.cod_regimen, r.cod_profesion," +
				" e.id_nivel_gobierno, e.id_pliego, sec.id_sector, e.id_ejecutora, " +
				" to_char(p.fec_nacimiento, 'dd/MM/yyyy') as FEC_NACIMIENTO ," +
				" nvl(substr(e.ubigeo,1,2), 0) as coddep, " +
				" nvl(substr(e.ubigeo,3,2), 0) as codpro, " +
				" nvl(substr(e.ubigeo,5,2), 0) as coddis, " +
				" nvl(rea.plazo,0) as plazofinal, " +
				" (SELECT TO_NUMBER(DESCRIPCION)  AS PLAZO FROM codigo WHERE GRUPO = 'PLHA' AND codigo = 'PLAZ') as plazo," +
				" (select to_char(sysdate + 1, 'dd/MM/yyyy') from dual) as AHORA, " +
				" case when nvl(rea.estado_registro, '0') = '1' then 'checked' else '' end  estadoHabilitado, " +
				" case when nvl(p.estado_validacion, '0') = '1' then 'checked' else '' end  estadoValidacion " +
				" from responsable r join persona p on r.dni = p.dni " +
				" join responsable_ejecutora_ambito rea on rea.id_responsable = r.id_responsable " +
				" join ejecutora e on e.id_ejecutora = rea.id_ejecutora " +
				" join pliego pli on pli.id_pliego = e.id_pliego join sector sec on sec.id_sector = pli.id_sector " +
				" join ambito am on am.id_ambito = rea.id_ambito join sistema s on s.id_sistema = am.id_sistema " +
				" where 1 =  1 and r.id_responsable = " + idResponsable.toString();	
			List<Map<String, Object>> lSectorMaps = jdbcTemplate.queryForList(sql);
			for(Map<String, Object> rec : lSectorMaps){
				// ID_RESPONSABLE	DNI	APELLIDO_PATERNO	APELLIDO_MATERNO
				// NOMBRES	DESCRIPCIONEJECUTORA	FECHABILITACION COD_TIPO_DOC CORREO   
				// SEXO COD_REGIMEN COD_PROFESION ID_NIVEL_GOBIERNO      
				// ID_PLIEGO	ID_SECTOR	ID_EJECUTORA	CODDEP	CODPRO	CODDIS
				obj = new ResponsableBusqueda();
				obj.setIdResponsable((Number)rec.get("ID_RESPONSABLE"));
				obj.setDni((String)rec.get("DNI"));
				obj.setApellidos_paterno((String)rec.get("APELLIDO_PATERNO"));
				obj.setApellidos_materno((String)rec.get("APELLIDO_MATERNO"));
				obj.setNombres((String)rec.get("NOMBRES"));
				obj.setDescripcionEjecutora((String)rec.get("DESCRIPCIONEJECUTORA"));
				obj.setFecHabilitacion((String)rec.get("FECHABILITACION"));
				obj.setCodTipoDocumento((String)rec.get("COD_TIPO_DOC"));
				obj.setCorreo((String)rec.get("CORREO"));
				obj.setSexo((String)rec.get("SEXO"));
				obj.setCodRegimen((String)rec.get("COD_REGIMEN"));
				obj.setCodProfesion((String)rec.get("COD_PROFESION"));
				obj.setIdNivelGobierno((Number)rec.get("ID_NIVEL_GOBIERNO"));
				obj.setIdPliego((Number)rec.get("ID_PLIEGO"));
				obj.setIdSector((Number)rec.get("ID_SECTOR"));
				obj.setIdEjecutora((Number)rec.get("ID_EJECUTORA")); 
				obj.setCoddep((String)rec.get("CODDEP"));
				obj.setCodpro((String)rec.get("CODPRO"));
				obj.setCoddis((String)rec.get("CODDIS"));
				obj.setEstadoHabilitado((String)rec.get("ESTADOHABILITADO"));
				obj.setEstadoValidacion((String)rec.get("ESTADOVALIDACION"));
				obj.setFecNacimiento((String)rec.get("FEC_NACIMIENTO"));
				obj.setPlazo(((Number)rec.get("PLAZO")).intValue());
				obj.setAhora((String)rec.get("AHORA"));
				obj.setPlazoFinal(((Number)rec.get("PLAZOFINAL")).intValue());
			}
			return obj;
		}

		public void grabarResponsable(JspResponsableEdicion obj) {
			// grabar datos de persona
			String sql = "";
			
			/*
			// vemos si cambio el dni 
			if (!obj.getPersona().getDni().equals(obj.getDniBase()))
			{
				sql = "UPDATE PERSONA SET DNI = ? " +
				" WHERE trim(DNI) = trim(?)";
				jdbcTemplate.update(sql, obj.getDni(), obj.getDniBase());				
			}
			*/
			int idEjecutora = 0;
			int idResponsable = 0;
			int idAmbito = 0;
			//*************************************
			//Registramos los datos de la persona
			//*************************************
			int seq = this.RegistrarPersona(obj.getPersona());
			
			idResponsable = obj.getResponsable().getId_responsable().intValue();
			idAmbito = obj.getId_ambito().intValue();

			// grabar habilitacion
			// select correo, cod_profesion, cod_regimen  from responsable 	id_responsable
			sql = "UPDATE responsable SET correo= upper(?), cod_profesion = ?" +
			", cod_regimen = ?, DNI = ? , DNIPREVIO = ?, id_diagnostico = ?, id_persona = ? " +
			" WHERE id_responsable = ?";
			jdbcTemplate.update(sql, obj.getResponsable().getCorreo(), obj.getResponsable().getCod_profesion()
						, obj.getResponsable().getCod_regimen(), obj.getPersona().getDni(), 
						obj.getPersona().getDni(), 
						obj.getResponsable().getId_diagnostico(),
						obj.getResponsable().getId_persona(),
						obj.getResponsable().getId_responsable());
			
			// grabar datos de la Entidad 
			if ( obj.getEjecutora().getId_ejecutora().intValue() <= 0 )
			{
					sql = "select seq_id_ejecutora.NEXTVAL from dual";
					idEjecutora = jdbcTemplate.queryForInt(sql);
					// insertamos nueva ejecutora
					sql = "INSERT INTO EJECUTORA (id_ejecutora, descripcion, ubigeo, ubigeo_rpt, " +
							"id_nivel_gobierno, estado) VALUES(" +
							"?,upper(?),?,?,?,'1')"	;
					jdbcTemplate.update(sql, idEjecutora, obj.getNombre_ejecutora(), obj.getEjecutora().getUbigeo(),
							obj.getEjecutora().getUbigeo_rpt(), obj.getEjecutora().getId_nivel_gobierno());

			}
			else
			{
				idEjecutora = obj.getEjecutora().getId_ejecutora().intValue();
				// actualizar datos de la entidad
				sql = "update EJECUTORA set ubigeo = ?, ubigeo_rpt = ?,  " +  //set descripcion = ?,
				"id_nivel_gobierno = ? where id_ejecutora = ?";
				jdbcTemplate.update(sql, obj.getEjecutora().getUbigeo(), obj.getEjecutora().getUbigeo_rpt(), // obj.getDescripcionEjecutora(), 
						obj.getEjecutora().getId_nivel_gobierno(), obj.getEjecutora().getId_ejecutora());
				
				if (obj.getEjecutora().getId_nivel_gobierno() != null && obj.getEjecutora().getId_nivel_gobierno().intValue() != 2)
				{
					//Actualizamos el nombre
					if (obj.getNombre_ejecutora() != null && obj.getNombre_ejecutora() != "")
					{
						sql = "update EJECUTORA set descripcion = upper(?) where id_ejecutora = ?";
						jdbcTemplate.update(sql, obj.getNombre_ejecutora(), obj.getEjecutora().getId_ejecutora());
					}										
				}
			}
			//Registramos la relacion responsable_ejecutora_ambito Considerar que un responsable solo
			// esta asignado a una entidad en un ambito de no se asi habra problemas en la grilla por
			// duplicidad de campo ID
			

			// se borra toda relacion con otro ejecutora  
		    sql = "DELETE FROM RESPONSABLE_EJECUTORA_AMBITO WHERE ID_RESPONSABLE = ? AND ID_AMBITO = ? AND id_ejecutora <> ? ";
		    jdbcTemplate.update(sql, idResponsable, obj.getId_ambito(),idEjecutora);

		    
			sql = "Select count(*) from RESPONSABLE_EJECUTORA_AMBITO where ID_EJECUTORA = ? and id_responsable = ? and id_ambito = ?";						
			int iCont = jdbcTemplate.queryForInt(sql,idEjecutora,idResponsable,obj.getId_ambito());

			/*
			if (iCont == 0) {
				sql = "INSERT INTO RESPONSABLE_EJECUTORA_AMBITO (ID_EJECUTORA,ID_RESPONSABLE, ID_AMBITO, ESTADO,FEC_HABILITACION, PLAZO,FEC_CIERRE,ESTADO_REGISTRO, FEC_ASIGNACION) values " +
				"  (?,?,?,'1',to_date(?,'DD/MM/YYYY'),?,f_obtener_fecha_cierre(to_date(?,'DD/MM/YYYY'),?),?, SYSDATE) " ;
				jdbcTemplate.update(sql, idEjecutora, idResponsable, obj.getId_ambito(),obj.getFec_habilitacion(),obj.getPlazo(),obj.getFec_habilitacion(),obj.getPlazo(),obj.getEstado_habilitado());
			}
			else{
				sql = "UPDATE RESPONSABLE_EJECUTORA_AMBITO set FEC_ASIGNACION = SYSDATE, FEC_HABILITACION = to_date(?,'DD/MM/YYYY'), PLAZO = ?,FEC_CIERRE = f_obtener_fecha_cierre(to_date(?,'DD/MM/YYYY'),?),ESTADO_REGISTRO = ? " +
				"  where id_ejecutora = ? and id_responsable = ? and id_ambito = ? " ;
				jdbcTemplate.update(sql, obj.getFec_habilitacion(),obj.getPlazo(),obj.getFec_habilitacion(),obj.getPlazo(),obj.getEstado_habilitado(),idEjecutora, idResponsable, obj.getId_ambito());				
				}				
			*/

			if (iCont == 0) {
				sql = "INSERT INTO RESPONSABLE_EJECUTORA_AMBITO (ID_EJECUTORA,ID_RESPONSABLE, ID_AMBITO, ESTADO,ESTADO_REGISTRO, FEC_ASIGNACION) values " +
				"  (?,?,?,'1','0',SYSDATE) " ;
				jdbcTemplate.update(sql, idEjecutora, idResponsable, obj.getId_ambito());
			}
			else{
				sql = "UPDATE RESPONSABLE_EJECUTORA_AMBITO set FEC_ASIGNACION = SYSDATE " +
				"  where id_ejecutora = ? and id_responsable = ? and id_ambito = ? " ;
				jdbcTemplate.update(sql, idEjecutora, idResponsable, obj.getId_ambito());				
				}				
			
			//sql = "Select count(*) from RESPONSABLE_EJECUTORA_AMBITO where ID_EJECUTORA = ? and id_responsable = ? and id_ambito = ?";						
			//int iCont = jdbcTemplate.queryForInt(sql,idEjecutora,idResponsable,idAmbito);
			//if (iCont == 0) {

				//sql = "UPDATE RESPONSABLE_EJECUTORA_AMBITO SET ESTADO = '0' WHERE ID_RESPONSABLE = ? AND ID_AMBITO = ? ";
				//jdbcTemplate.update(sql, idResponsable, obj.getId_ambito());
				// decidimos eliminar el registro 
			    //sql = "DELETE FROM RESPONSABLE_EJECUTORA_AMBITO WHERE ID_RESPONSABLE = ? AND ID_AMBITO = ? ";
			    //jdbcTemplate.update(sql, idResponsable, obj.getId_ambito());
				// INSERTAMOS LA NUEVA RELACION
				//sql = "INSERT INTO RESPONSABLE_EJECUTORA_AMBITO (ID_EJECUTORA,ID_RESPONSABLE, ID_AMBITO, ESTADO) values " +
				//"  (?,?,?,'1') " ;
				//jdbcTemplate.update(sql, idEjecutora, idResponsable, obj.getId_ambito());
			//}
			
			if (obj.getServicioCivil() == null)
				crearServicioCivilResponsable(obj);
			else
				actualizarServicioCivilResponsable(obj);
			
		}

		public LinkedList<Ejecutora> ObtenerEjecutoras() {
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList("select id_ejecutora, descripcion from ejecutora WHERE " +
						" estado = '1' order by descripcion");
			LinkedList<Ejecutora> obj = new LinkedList<Ejecutora>();
			for(Map<String, Object> rec : lSectorMaps){
				Ejecutora s = new Ejecutora();
				s.setId_ejecutora((Number)rec.get("ID_EJECUTORA"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				obj.add(s);
			}
			return obj;
		}

		public LinkedList<Ejecutora> ObtenerEjecutorasNivelGobierno(
				Integer idNivelGobierno,String ubigeo) {
			
			/*String sql ="select -1 id_ejecutora, 'NUEVO' as descripcion, 1 prioridad  from dual union all " + 
				" select id_ejecutora, descripcion, 2 prioridad from ejecutora WHERE " +
				" id_nivel_gobierno = " + idNivelGobierno  ;
			if (idNivelGobierno != 2)
				sql = sql + " and ubigeo = '" + ubigeo + "'";			
			sql = sql + " and estado = '1' order by prioridad, descripcion";*/

			String sql = " SELECT id_ejecutora, descripcion from ejecutora WHERE " +
					" id_nivel_gobierno = " + idNivelGobierno +  
					" and estado = '1' order by descripcion";
			
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList(sql);
			LinkedList<Ejecutora> obj = new LinkedList<Ejecutora>();
			for(Map<String, Object> rec : lSectorMaps){
				Ejecutora s = new Ejecutora();
				s.setId_ejecutora((Number)rec.get("ID_EJECUTORA"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				obj.add(s);
			}
			return obj;
}

		public void grabarValidacion(ServicioCivil obj) {
			String sql = "";
			
			sql = "update servicio_civil set cod_estado = 'VALI' " +
			  " where id_servicio_civil = ? ";
				jdbcTemplate.update(sql, obj.getId_servicio_civil());
			
			sql = "update persona set estado_validacion = '1', fec_validacion = sysdate " +
				  " where dni = ? and id_persona = ?";
					jdbcTemplate.update(sql, obj.getDni(), obj.getId_persona());

			sql = "update ejecutora set estado = '1'" +
					  " where id_ejecutora = ? ";
						jdbcTemplate.update(sql, obj.getId_ejecutora());
					
			// ejecutar el envio de correos 
			
						
			/* @JZM-23012013 Arson
			 * Se anula la forma de llamada al SP para poder obtener los parametros de salida
			 */
			//sql = "call correo_valida_participante(" + obj.getId_servicio_civil() + ")";
			//jdbcTemplate.update(sql);
			try{
				sql = "{call MAIL_CENSO.CORREO_VALIDA_PARTICIPANTE(?,?,?,?,?)}";
				conn = this.dataSourceCenso.getConnection();
				callableStatement = conn.prepareCall(sql);

				callableStatement.setLong(1, obj.getId_servicio_civil().longValue());
				callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR); // PSENDER
				callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR); // PRECIPIENT
				callableStatement.registerOutParameter(4, java.sql.Types.VARCHAR); // PSUBJECT
				callableStatement.registerOutParameter(5, java.sql.Types.VARCHAR); // PCONTENIDOFINAL
				callableStatement.executeUpdate();

				// Se activa el env�o del correo a trav�s de Java
				SMTPSend.Mail(callableStatement.getString(4), callableStatement.getString(5),callableStatement.getString(3));
			} catch (SQLException  e) {
				e.printStackTrace();
			} finally {
				if (!(callableStatement == null)) {
					try {
						callableStatement.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				if (!(conn == null)) {
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
						
		}

		public void grabarValidacion(Persona persona) {
			String sql = "";
			sql = "update persona set estado_validacion = '1', fec_validacion = sysdate " +
				  " where dni = ? and id_persona = ?";
					jdbcTemplate.update(sql, persona.getDni(), persona.getId_persona());
			sql = "update servicio_civil set cod_estado = 'VALI' " +
					  " where dni = ? and id_persona = ? ";
						jdbcTemplate.update(sql, persona.getDni(), persona.getId_persona());
		}

		
		public void grabarObservacion(ServicioCivil obj)
		{
			String sql = "";
			sql = "update servicio_civil set estado_observacion = '1', fec_observacion = sysdate, " +
				  " detalle_observacion = ?, cod_tipo_observacion = ?, plazo = ?, cod_estado = 'OBSE', " +
				  " estado_subsanacion = '0' " +
				  " where id_servicio_civil = ? ";
			jdbcTemplate.update(sql, obj.getDetalle_observacion(), obj.getCod_tipo_observacion()
							, obj.getPlazo(), obj.getId_servicio_civil());
			
			// ejecutar el envio de correos 
			/* @JZM-23012013 Arson
			 * Se anula la forma de llamada al SP para poder obtener los parametros de salida
			 */
			//sql = "call correo_observ_participante(" + obj.getId_servicio_civil() + ")";
			//jdbcTemplate.update(sql);
			
			try{
				sql = "{call MAIL_CENSO.CORREO_OBSERV_PARTICIPANTE(?,?,?,?,?)}";
				conn = this.dataSourceCenso.getConnection();
				callableStatement = conn.prepareCall(sql);

				callableStatement.setLong(1, obj.getId_servicio_civil().longValue());
				callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR); // PSENDER
				callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR); // PRECIPIENT
				callableStatement.registerOutParameter(4, java.sql.Types.VARCHAR); // PSUBJECT
				callableStatement.registerOutParameter(5, java.sql.Types.VARCHAR); // PCONTENIDOFINAL
				callableStatement.executeUpdate();

				// Se activa el env�o del correo a trav�s de Java
				SMTPSend.Mail(callableStatement.getString(4), callableStatement.getString(5),callableStatement.getString(3));
			} catch (SQLException  e) {
				e.printStackTrace();
			} finally {
				if (!(callableStatement == null)) {
					try {
						callableStatement.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				if (!(conn == null)) {
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
			
		}
		public void grabarSubsanacion(ServicioCivil obj)
		{
			// el resgistro del participante vuelva al estado confirmado de esa forma
			// no puede editarse 
			String sql = "";
			sql = "update servicio_civil set estado_observacion = '0', fec_subsanacion = sysdate, " +
				  " detalle_subsanacion = ?, estado_subsanacion = '1', cod_estado = 'CONF' " +
				  " where id_servicio_civil = ? ";
			jdbcTemplate.update(sql, obj.getDetalle_subsanacion(), obj.getId_servicio_civil());
			
			// ejecutar el envio de correos 
			// sql = "call correo_observ_participante(" + obj.getId_servicio_civil() + ")";
			// jdbcTemplate.update(sql);
		}
		
		public void crearServicioCivilResponsable(JspResponsableEdicion obj) {
			
			// String sql = "call sp_del_servicio_civil_res(?,?,?)";
			// jdbcTemplate.update(sql, obj.getPersona().getDni(), obj.getPersona().getId_persona(), obj.getId_ambito());

			String sql = "";

			Number idServicioCivil = this.GetLastIdServicioCivil();
			
			sql = "INSERT into servicio_civil(id_servicio_civil,correo,telefono,id_ejecutora,cod_profesion,cod_regimen,clave, fec_registro, cod_estado,dni, dniprevio, jefe, id_persona)"
					+" select "+idServicioCivil+" id_servicio_civil,correo,telefono,(select max(id_ejecutora) from responsable_ejecutora_ambito ram where ram.id_responsable = r.id_responsable) as id_ejecutora"
					+" ,cod_profesion,cod_regimen,clave, sysdate fec_registro,'VALI' cod_estado,dni,dni,1 jefe, " + obj.getPersona().getId_persona() + " id_persona " 
					+" from responsable r "
					+" where id_responsable = "+ obj.getResponsable().getId_responsable();
			jdbcTemplate.update(sql);
			
			sql = "INSERT into servicio_civil_ambito(ID_SERVICIO_CIVIL, ID_AMBITO) " +
					" VALUES(?,?) ";
			jdbcTemplate.update(sql, idServicioCivil, obj.getId_ambito());
			
			sql = "insert into servicio_civil_actividad(id_servicio_civil,id_actividad)" +
					" Select "+idServicioCivil+" ,a.id_actividad from actividad a, funcion f, etapa e" +
					" where a.id_funcion = f.id_funcion " +
					" and f.id_etapa = e.id_etapa" +
					" and e.id_ambito = "+obj.getId_ambito();
			jdbcTemplate.update(sql);
			
		}
		
		public void actualizarServicioCivilResponsable(JspResponsableEdicion obj) {

			String sql = "";
			sql = "update servicio_civil set correo = upper(?), id_ejecutora = ?, cod_profesion = ? ,cod_regimen = ?," +
					"clave = ?, fec_registro=sysdate, dni = ?, dniprevio= ?, jefe=1, id_persona = ? "
				+" where id_servicio_civil = "+ obj.getServicioCivil().getId_servicio_civil();
			jdbcTemplate.update(sql, obj.getResponsable().getCorreo(),  obj.getEjecutora().getId_ejecutora(), obj.getResponsable().getCod_profesion(),
					obj.getResponsable().getCod_regimen(), obj.getResponsable().getClave(), obj.getResponsable().getDni(), obj.getResponsable().getDni(), obj.getResponsable().getId_persona());
			
			sql = "delete from servicio_civil_actividad where id_servicio_civil = " + obj.getServicioCivil().getId_servicio_civil();
			jdbcTemplate.update(sql);

			sql = "insert into servicio_civil_actividad(id_servicio_civil,id_actividad)" +
			" Select "+ obj.getServicioCivil().getId_servicio_civil() +" ,a.id_actividad from actividad a, funcion f, etapa e" +
			" where a.id_funcion = f.id_funcion " +
			" and f.id_etapa = e.id_etapa" +
			" and e.id_ambito = "+obj.getId_ambito();
			jdbcTemplate.update(sql);

		}
		
		public void grabarHabilitacion(JspResponsableEdicion obj) {
			
			String sql = "";
			//registramos al responsable como participante
			
			if (obj.getServicioCivil() == null)
				crearServicioCivilResponsable(obj);
			else
				actualizarServicioCivilResponsable(obj);
			
			
			sql = "update RESPONSABLE_EJECUTORA_AMBITO set ESTADO_REGISTRO = '1', FEC_HABILITACION = " +
				  " to_date('" + obj.getFec_habilitacion()  + "', 'dd/MM/yyyy'), " +
						  " plazo = " + obj.getPlazo() + ", FEC_CIERRE = " +
			  	  "f_obtener_fecha_cierre(to_date( '"  + obj.getFec_habilitacion() + " 23:59', 'dd/mm/yyyy HH24:MI') , " + obj.getPlazo() +
						  ") where id_responsable = " + obj.getResponsable().getId_responsable() +
						  " and id_ambito = "+ obj.getId_ambito();
					jdbcTemplate.update(sql);

			/* @JZM-23012013 Arson
			 * Se anula la forma de llamada al SP para poder obtener los parametros de salida
			 */
			/*
			sql = "call correo_habilitacion(" + obj.getResponsable().getId_responsable() + "," + obj.getId_ambito() + ")";
			try {
				jdbcTemplate.update(sql);
			} catch (DataAccessException e) {
				e.printStackTrace();
			}
			*/
			try {
				sql = "{call MAIL_CENSO.CORREO_HABILITACION(?,?,?,?,?,?)}";
				conn = this.dataSourceCenso.getConnection();
				callableStatement = conn.prepareCall(sql);

				callableStatement.setLong(1, obj.getResponsable().getId_responsable().longValue());
				callableStatement.setLong(2, obj.getId_ambito().longValue());
				callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR); // PSENDER
				callableStatement.registerOutParameter(4, java.sql.Types.VARCHAR); // PRECIPIENT
				callableStatement.registerOutParameter(5, java.sql.Types.VARCHAR); // PSUBJECT
				callableStatement.registerOutParameter(6, java.sql.Types.VARCHAR); // PCONTENIDOFINAL
				callableStatement.executeUpdate();

				// Se activa el env�o del correo a trav�s de Java
				SMTPSend.Mail(callableStatement.getString(5), callableStatement.getString(6),callableStatement.getString(3));
			} catch (SQLException  e) {
				e.printStackTrace();
			} finally {
				if (!(callableStatement == null)) {
					try {
						callableStatement.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				if (!(conn == null)) {
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
			
					
		}

		public Integer grabarNuevoResponsable(JspResponsableEdicion obj) {
			// grabar datos de persona
			String sql = "";
			int idEjecutora = 0;
			int idResponsable = 0;
			//*************************************
			//Registramos los datos de la persona
			//*************************************
			int seq_id_persona = this.RegistrarPersona(obj.getPersona());			

			// Obtener IdResponsable
			sql = "SELECT SEQ_ID_RESPONSABLE.NEXTVAL from dual";
			idResponsable = jdbcTemplate.queryForInt(sql);
			
			// grabar responsable
			sql = "INSERT into responsable (correo, cod_profesion, cod_regimen, id_responsable, "+
				" DNI, ESTADO, COD_ROL_RESPONSABLE,CLAVE, ID_PERSONA, DNIPREVIO, id_diagnostico) values (" +
				" ?,?,?,?,?,'1','RESP',null," + seq_id_persona + ",?,?) ";
			jdbcTemplate.update(sql, obj.getResponsable().getCorreo(), obj.getResponsable().getCod_profesion(), obj.getResponsable().getCod_regimen(), 
					idResponsable, obj.getPersona().getDni(), obj.getPersona().getDni(), obj.getResponsable().getId_diagnostico());
			
			// grabar datos de la Entidad 
			if ( obj.getEjecutora().getId_ejecutora() == null || obj.getEjecutora().getId_ejecutora().intValue() <= 0 )
			{
					sql = "select seq_id_ejecutora.NEXTVAL from dual";
					idEjecutora = jdbcTemplate.queryForInt(sql);
					// insertamos nueva ejecutora
					sql = "INSERT INTO EJECUTORA (id_ejecutora, descripcion, ubigeo, ubigeo_rpt, " +
							"id_nivel_gobierno, estado) VALUES(" +
							"?,upper(?),?,?,?,'1')"	;
					jdbcTemplate.update(sql, idEjecutora, obj.getNombre_ejecutora(), obj.getEjecutora().getUbigeo(),
							obj.getEjecutora().getUbigeo_rpt(), obj.getEjecutora().getId_nivel_gobierno());
			}
			else
			{
				idEjecutora = obj.getEjecutora().getId_ejecutora().intValue();
				// actualizar datos de la entidad
				sql = "UPDATE EJECUTORA set ubigeo = ?, ubigeo_rpt = ?, " + //descripcion = ?,  
				"id_nivel_gobierno = ? where id_ejecutora = ?";
				jdbcTemplate.update(sql, obj.getEjecutora().getUbigeo(), obj.getEjecutora().getUbigeo_rpt(), // obj.getDescripcionEjecutora(), 
						obj.getEjecutora().getId_nivel_gobierno(), obj.getEjecutora().getId_ejecutora());
				
				if (obj.getEjecutora().getId_nivel_gobierno() != null && obj.getEjecutora().getId_nivel_gobierno().intValue() != 2)
				{
					//Actualizamos el nombre
					if (obj.getNombre_ejecutora() != null && obj.getNombre_ejecutora() != "")
					{
						sql = "update EJECUTORA set descripcion = upper(?) where id_ejecutora = ?";
						jdbcTemplate.update(sql, obj.getNombre_ejecutora(), obj.getEjecutora().getId_ejecutora());
					}										
				}
			}

			// TODO Verificar como tratar el tema de cambios de entidades 
			// Registramos la relacion responsable_ejecutora_ambito
			//sql = "Select count(*) from RESPONSABLE_EJECUTORA_AMBITO where ID_EJECUTORA = ? and id_responsable = ? and id_ambito = ?";						
			//int iCont = jdbcTemplate.queryForInt(sql,idEjecutora,idResponsable,obj.getId_ambito());
			
			// se borra toda relacion de RESPONSABLE_EJECUTORA_AMBITO  
		    sql = "DELETE FROM RESPONSABLE_EJECUTORA_AMBITO WHERE ID_RESPONSABLE = ? AND ID_AMBITO = ? ";
		    jdbcTemplate.update(sql, idResponsable, obj.getId_ambito());
		    
			//if (iCont == 0) {
				sql = "INSERT INTO RESPONSABLE_EJECUTORA_AMBITO (ID_EJECUTORA,ID_RESPONSABLE, ID_AMBITO, ESTADO,FEC_ASIGNACION) values " +
				"  (?,?,?,'1',SYSDATE) " ;
				jdbcTemplate.update(sql, idEjecutora, idResponsable, obj.getId_ambito());
			/*	
			}
			else{
				
				sql = "UPDATE RESPONSABLE_EJECUTORA_AMBITO set FEC_ASIGNACION = SYSDATE, FEC_HABILITACION = to_date(?,'DD/MM/YYYY'), PLAZO = ?,FEC_CIERRE = f_obtener_fecha_cierre(to_date(?,'DD/MM/YYYY'),?),ESTADO_REGISTRO = ? " +
				"  where id_ejecutora = ? and id_responsable = ? and id_ambito = ? " ;
				jdbcTemplate.update(sql, obj.getFec_habilitacion(),obj.getPlazo(),obj.getFec_habilitacion(),obj.getPlazo(),obj.getEstado_habilitado(),idEjecutora, idResponsable, obj.getId_ambito());				
			
				
				sql = "UPDATE RESPONSABLE_EJECUTORA_AMBITO set FEC_HABILITACION = to_date(?,'DD/MM/YYYY'), PLAZO = ?,FEC_CIERRE = f_obtener_fecha_cierre(to_date(?,'DD/MM/YYYY'),?),ESTADO_REGISTRO = ? " +
				"  where id_ejecutora = ? and id_responsable = ? and id_ambito = ? " ;
				jdbcTemplate.update(sql, obj.getFec_hMIGUabilitacion(),obj.getPlazo(),obj.getFec_habilitacion(),obj.getPlazo(),obj.getEstado_habilitado(),idEjecutora, idResponsable, obj.getId_ambito());				
			}*/
				/*
			if (obj.getEstado_habilitado() != null && obj.getEstado_habilitado().intValue() == 1)
			{
				sql = "call correo_habilitacion(" + obj.getResponsable().getId_responsable() + "," + obj.getId_ambito() + ")";
				try {
					jdbcTemplate.update(sql);
				} catch (DataAccessException e) {
					e.printStackTrace();
				}
				//call correo_habilitacion(id_responsable,id_ambito)
			}*/
			
				return idResponsable;
			
		}
		
		public Responsable GetResponsable(Number idResponsable){
			Responsable obj = null;

			String sql = "SELECT res.ID_RESPONSABLE,res.ID_PERSONA, res.dni, res.dniprevio, res.CORREO, res.COD_PROFESION, res.COD_REGIMEN, " +
					" res.CLAVE, res.ESTADO,res.COD_ROL_RESPONSABLE, res.TELEFONO, res.ID_DIAGNOSTICO, " +
					" (select count(id_servicio_civil) from servicio_civil " + 
					" where id_ejecutora = rea.id_ejecutora) as inscritos " +
					" FROM RESPONSABLE res join responsable_ejecutora_ambito rea on " +
					" res.id_responsable = rea.id_responsable " +
					" join ejecutora ej on ej.id_ejecutora = rea.id_ejecutora " +
					" WHERE res.id_responsable = " + idResponsable ;
			List<Map<String, Object>> lEntidadMaps = jdbcTemplate.queryForList(sql);

			for (Map<String, Object> rec : lEntidadMaps) {
				obj = new Responsable();
				obj.setId_responsable((Number) rec.get("ID_RESPONSABLE"));
				obj.setId_persona((Number)rec.get("ID_PERSONA"));
				obj.setId_diagnostico((Number)rec.get("ID_DIAGNOSTICO"));
				obj.setDni((String) rec.get("DNI"));
				obj.setDniprevio((String) rec.get("DNIPREVIO"));
				obj.setCorreo((String) rec.get("CORREO"));
				obj.setCod_profesion((String) rec.get("COD_PROFESION"));
				obj.setCod_regimen((String) rec.get("COD_REGIMEN"));
				obj.setClave((String) rec.get("CLAVE"));
				obj.setEstado((String) rec.get("ESTADO"));
				obj.setCod_rol_responsable((String) rec.get("COD_ROL_RESPONSABLE"));				
				obj.setTelefono((String) rec.get("TELEFONO"));
				obj.setInscritos(((Number) rec.get("INSCRITOS")).intValue());
				
			}
			return obj;
		}

		public Responsable GetResponsablePorDNI(String dni){
			Responsable obj = null;

			String sqlSelect = "SELECT res.ID_RESPONSABLE, res.DNI, res.CORREO, COD_PROFESION, res.COD_REGIMEN, res.CLAVE, res.ESTADO, res.COD_ROL_RESPONSABLE," +
					" res.TELEFONO, res.ID_PERSONA, res.DNIPREVIO, res.ID_DIAGNOSTICO," +
					" (select count(sc.id_servicio_civil) from servicio_civil sc" +
					" where sc.id_ejecutora = rea.id_ejecutora) as inscritos" +
					" FROM RESPONSABLE res join responsable_ejecutora_ambito rea " +
					" on res.id_responsable = rea.id_responsable " +
					" join ejecutora ej on ej.id_ejecutora = rea.id_ejecutora " +
					" where res.dni = '" + dni.trim() + "'" ;
			List<Map<String, Object>> lEntidadMaps = jdbcTemplate.queryForList(sqlSelect);

			for (Map<String, Object> rec : lEntidadMaps) {
				obj = new Responsable();
				obj.setId_responsable((Number) rec.get("ID_RESPONSABLE"));
				obj.setDni((String) rec.get("DNI"));
				obj.setCorreo((String) rec.get("CORREO"));
				obj.setCod_profesion((String) rec.get("COD_PROFESION"));
				obj.setCod_regimen((String) rec.get("COD_REGIMEN"));
				obj.setClave((String) rec.get("CLAVE"));
				obj.setEstado((String) rec.get("ESTADO"));
				obj.setCod_rol_responsable((String) rec.get("COD_ROL_RESPONSABLE"));				
				obj.setTelefono((String) rec.get("TELEFONO"));
				Number insc = ((Number)rec.get("INSCRITOS"));
				obj.setInscritos(insc.intValue());
			}
			return obj;			
		}
		
		public Responsable GetResponsablePorEjecutora(Number idEjecutora)
		{
			Responsable obj = null;

			String sql = "SELECT res.ID_RESPONSABLE, res.DNI, res.CORREO, COD_PROFESION, res.COD_REGIMEN, CLAVE, res.ESTADO, res.COD_ROL_RESPONSABLE," +
					" res.TELEFONO, res.ID_PERSONA, res.DNIPREVIO, res.ID_DIAGNOSTICO," +
					" (select count(id_servicio_civil) from servicio_civil" +
					" where id_ejecutora = rea.id_ejecutora) as inscritos" +
					" FROM RESPONSABLE res join responsable_ejecutora_ambito rea " +
					" on res.id_responsable = rea.id_responsable " +
					" join ejecutora ej on ej.id_ejecutora = rea.id_ejecutora " +
					" where rea.id_ejecutora = " + idEjecutora.toString();
			List<Map<String, Object>> lEntidadMaps = jdbcTemplate.queryForList(sql);

			for (Map<String, Object> rec : lEntidadMaps) {
				obj = new Responsable();
				obj.setId_responsable((Number) rec.get("ID_RESPONSABLE"));
				obj.setDni((String) rec.get("DNI"));
				obj.setCorreo((String) rec.get("CORREO"));
				obj.setCod_profesion((String) rec.get("COD_PROFESION"));
				obj.setCod_regimen((String) rec.get("COD_REGIMEN"));
				obj.setClave((String) rec.get("CLAVE"));
				obj.setEstado((String) rec.get("ESTADO"));
				obj.setCod_rol_responsable((String) rec.get("COD_ROL_RESPONSABLE"));				
				obj.setTelefono((String) rec.get("TELEFONO"));
				Number insc = ((Number)rec.get("INSCRITOS"));
				obj.setInscritos(insc.intValue());
				
			}
			return obj;			
		}

		

		public Diagnostico GetDiagnostico(Number idDiagnostico)
		{
			List<Map<String, Object>> lSectorMaps = jdbcTemplate.queryForList("select id_diagnostico, " +
					" descripcion, to_char(fec_prueba, 'dd/mm/yyy') as fec_prueba, to_char(fec_ini_habilitacion,'dd/mm/yyy') as fec_ini_habilitacion, " +
					" to_char(fec_fin_habilitacion, 'dd/mm/yyy') as fec_fin_habilitacion, to_char(fec_ini_registro, 'dd/mm/yyy') as fec_ini_registro, to_char(fec_fin_registro, 'dd/mm/yyy') as fec_fin_registro, estado " +
					" from diagnostico where id_diagnostico = " + idDiagnostico.intValue() +  " and estado = 1");
			Diagnostico s = new Diagnostico();	
			for(Map<String, Object> rec : lSectorMaps){
				s.setId_diagnostico((Number)rec.get("ID_DIAGNOSTICO"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));
				s.setFec_prueba((String)rec.get("FEC_PRUEBA"));
				s.setFec_ini_habilitacion((String)rec.get("FEC_INI_HABILITACION"));
				s.setFec_fin_habilitacion((String)rec.get("FEC_FIN_HABILITACION"));
				s.setFec_ini_registro((String)rec.get("FEC_INI_REGISTRO"));
				s.setFec_fin_registro((String)rec.get("FEC_FIC_REGISTRO"));
				s.setEstado((String)rec.get("ESTADO"));
	
			}
			return s;
				
		}
		
		
		
		public void EliminarParticipante(JspResponsableEdicion obj) {
			String sql = "call sp_del_responsable(" + obj.getResponsable().getId_responsable()+ "," + obj.getId_ambito() + ")";
			try { 
				jdbcTemplate.update(sql);
			} catch (DataAccessException e) {
				e.printStackTrace();
			}		
			
		}

		public LinkedList<GenOption> ObtenerEstadosInscritos() {
			String sql = "select codigo, descripcion from codigo where grupo = 'EPIN' order by orden";
	
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList(sql);
			LinkedList<GenOption> obj = new LinkedList<GenOption>();
			for(Map<String, Object> rec : lSectorMaps){
				GenOption s = new GenOption();
				s.setCodigo((String)rec.get("CODIGO"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				obj.add(s);
			}
			return obj;
		}

		public LinkedList<GenOption> ObtenerEstadosPlazo() {
			String sql = "select codigo, descripcion from codigo where grupo = 'EPLA' order by orden";
			
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList(sql);
			LinkedList<GenOption> obj = new LinkedList<GenOption>();
			for(Map<String, Object> rec : lSectorMaps){
				GenOption s = new GenOption();
				s.setCodigo((String)rec.get("CODIGO"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				obj.add(s);
			}
			return obj;
		}

		public LinkedList<GenOption> ObtenerEstadosResponsable() {
			String sql = "select codigo, descripcion from codigo where grupo = 'ERES' order by orden";
			
			List<Map<String, Object>> lSectorMaps = 
				jdbcTemplate.queryForList(sql);
			LinkedList<GenOption> obj = new LinkedList<GenOption>();
			for(Map<String, Object> rec : lSectorMaps){
				GenOption s = new GenOption();
				s.setCodigo((String)rec.get("CODIGO"));
				s.setDescripcion((String)rec.get("DESCRIPCION"));			
				obj.add(s);
			}
			return obj;
			}

		public String ObtenerClave(String dni, Integer idSistema) {
			String mensaje = "";
			CallableStatement cstmt = null;
			Connection conn = null;
			Object temp;
			
			try {
				conn = this.dataSourceCenso.getConnection();
				cstmt = conn.prepareCall("{call MAIL_CENSO.RECUPERAR_CLAVE_SERVICIO_CIVIL(?,?,?)}");
				cstmt.setInt(1, idSistema);
				cstmt.setString(2, dni);
				cstmt.registerOutParameter(3, OracleTypes.VARCHAR);
				cstmt.registerOutParameter(4, OracleTypes.VARCHAR); // PSENDER
				cstmt.registerOutParameter(5, OracleTypes.VARCHAR); // PRECIPIENT
				cstmt.registerOutParameter(6, OracleTypes.VARCHAR); // PSUBJECT
				cstmt.registerOutParameter(7, OracleTypes.VARCHAR); // PCONTENIDOFINAL
				cstmt.registerOutParameter(8, OracleTypes.INTEGER); // PVALIDACION
				cstmt.execute();
				mensaje = (String)cstmt.getObject(3);
				// Se activa el env�o del correo a trav�s de Java
				if (cstmt.getInt(8) > 0 ){
					SMTPSend.Mail(cstmt.getString(6), cstmt.getString(7),cstmt.getString(5));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return mensaje;
			
		}

		public LinkedList<TipoEstudio> getTipoEstudio() {
			LinkedList<TipoEstudio> obj = new LinkedList<TipoEstudio>();

			StringBuilder sql = new StringBuilder();
			sql.append("SELECT ID_TIPO_ESTUDIO,DES_TIPO_ESTUDIO FROM CENSOABAST.TIPO_ESTUDIO");

			List<Map<String, Object>> collec = jdbcTemplate.queryForList(sql.toString());
			for (Map<String, Object> rec : collec) {
				TipoEstudio tipoEstudio = new TipoEstudio();
				tipoEstudio.setId_tipo_estudio((Number) rec.get("ID_TIPO_ESTUDIO"));
				tipoEstudio.setDes_tipo_estudio((String) rec.get("DES_TIPO_ESTUDIO"));
				obj.add(tipoEstudio);
			}
			return obj;
		}
	}
